Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA90993 for ; Thu, 18 Nov 1999 19:45:00 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id TAA00360; Thu, 18 Nov 1999 19:43:54 -0500 (EST) To: Douglas Nichols cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] sql question: min remapped value In-reply-to: Your message of Thu, 18 Nov 1999 23:26:24 +0000 <38348B20.6A4F8EF1@fhcrc.org> Date: Thu, 18 Nov 1999 19:43:53 -0500 Message-ID: <358.942972233@sss.pgh.pa.us> From: Tom Lane Douglas Nichols writes: > I was looking at something like > select id, field1, min(decode(field1,1,1,0,2,3,4,4,5,0)) as flag > from mydata > group by id; Postgres doesn't have DECODE (nor is it in the SQL standard), but you can get the effect you want with a CASE expression. Note that CASE is rather buggy in pre-6.5.2 releases, so update if you haven't lately. PS: you won't be able to access field1 outside the MIN() if you have "group by id" as shown. regards, tom lane