Received: from smtp-out1.bellatlantic.net (smtp-out1.bellatlantic.net [199.45.39.61]) by hub.org (8.9.1/8.9.1) with ESMTP id VAA14621 for ; Wed, 2 Dec 1998 21:23:43 -0500 (EST) (envelope-from daybee@bellatlantic.net) Received: from bellatlantic.net (client-151-196-125-196.bellatlantic.net [151.196.125.196]) by smtp-out1.bellatlantic.net (8.9.1/8.9.1) with ESMTP id VAA26438; Wed, 2 Dec 1998 21:23:50 -0500 (EST) Message-ID: <3665F665.15AC16B4@bellatlantic.net> Date: Wed, 02 Dec 1998 21:24:37 -0500 From: David Hartwig Organization: Home X-Mailer: Mozilla 4.5b2 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: Greg Youngblood CC: "'PostgreSQL SQL List'" Subject: Re: [SQL] Please help: How to determine largest of two numbers in a query? References: <2B9713A99045D211BB4E0008C75668692EA70C@SCORPEXC1.houstoncell.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit My $.03 worth, without really knowing what these values represent: There is no such "standard" query that can conditionally select between two columns. Fortunately PostgreSQL will allow you to create function - of one does not already exist. The function would be something like: greater_int(integer a, integer b) It takes two integers arguments and returns the greater integer. Then you do something like: CREATE VIEW myview AS SELECT yyymmdd, key, greater_int(value1, value2) FROM mytable; Or just do the SELECTs directly. This may not be the most efficient way to accomplish your goal. I would also take a good look at may table design. Value1 and value2 MAY be a repeating group and you may want to normalize a little more. Or not. 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