Received: from trends.net (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.1/8.9.1) with ESMTP id NAA08700 for ; Thu, 3 Dec 1998 13:55:50 -0500 (EST) (envelope-from sferac@bo.nettuno.it) Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by trends.net (8.8.8/8.8.8) with ESMTP id HAA02258 for ; Thu, 3 Dec 1998 07:40:49 -0500 (EST) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id NAA05209; Thu, 3 Dec 1998 13:39:21 +0100 (MET) Received: from blu.sferacarta.com (mail.sferacarta.com) [10.20.30.12] by proxy.sferacarta.com with smtp (Exim 2.05 #1 (Debian)) id 0zlZlr-000387-00; Thu, 3 Dec 1998 14:30:19 +0000 Date: Thu, 3 Dec 1998 13:38:38 +0100 From: Sferacarta Software X-Mailer: The Bat! (v1.017) UNREG Reply-To: Sferacarta Software Organization: Sferacarta Software Priority: Normal Message-ID: <7568.981203@bo.nettuno.it> To: Greg Youngblood , David Hartwig , "'PostgreSQL SQL List'" Subject: Re[2]: [SQL] Please help: How to determine largest of two numbers in a query? References: <3665F665.15AC16B4@bellatlantic.net> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Hello David, giovedì, 3 dicembre 98, you wrote: DH> My $.03 worth, without really knowing what these values represent: DH> There is no such "standard" query that can conditionally select between two DH> columns. Fortunately PostgreSQL will allow you to create function - of one DH> does not already exist. DH> The function would be something like: DH> greater_int(integer a, integer b) DH> It takes two integers arguments and returns the greater integer. DH> Then you do something like: DH> CREATE VIEW myview AS DH> SELECT yyymmdd, key, greater_int(value1, value2) FROM mytable; DH> Or just do the SELECTs directly. DH> This may not be the most efficient way to accomplish your goal. I would also DH> take a good look at may table design. Value1 and value2 MAY be a repeating DH> group and you may want to normalize a little more. Or not. DH> Greg Youngblood wrote: >> I have a table with the following structure: >> yyyymmdd int4 >> key char(16) >> value1 int4 >> value2 int4 >> >> with the following sample data: >> yyyymmdd key value1 value2 >> 19981201 hello 32 16 >> 19981201 bye 29 64 >> 19981202 hello 16 20 >> 19981202 bye 23 13 >> >> What I need is to select the greatest between value1 and value2, so the >> answer would be: >> yyyymmdd key value >> 19981201 hello 32 >> 19981201 bye 64 >> 19981202 hello 20 >> 19981202 bye 23 >> >> I can do this via ODBC using access by creating a column which is defined as >> IF(value1>value2,value1,value2) but that doesn't work in psql. >> >> How can I make this work in psql? >> >> Thanks >> Greg PostgreSQL has already these built-in funcions (those used on aggregate MIN/MAX): int2 |int2larger |int2 int2 |larger of two int2 |int2smaller |int2 int2 |smaller of two int4 |int4larger |int4 int4 |larger of two int4 |int4smaller |int4 int4 |smaller of two int8 |int8larger |int8 int8 |larger of two int8 |int8smaller |int8 int8 |smaller of two exemple: SELECT int4larger(3,10) int4larger ---------- 10 (1 row) -Jose'-