pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsql question: min remapped value
2+ messages / 2 participants
[nested] [flat]
* sql question: min remapped value
@ 1999-11-18 23:26 Douglas Nichols <dnichols@fhcrc.org>
0 siblings, 1 reply; 2+ messages in thread
From: Douglas Nichols @ 1999-11-18 23:26 UTC (permalink / raw)
To: pgsql-sql
I am trying to remap a field find the row with the minimum value and
then keep the original value and the id number from that row something
like this:
mydata (id, field1)
field1 can = 0,1,2,3,4,5
but I reallt need them ordered like this (1=2, 0=2, 3=4, 4=5, 5=0)
once I find the lowest remapped value I need to know id & field1
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;
Any help is appreciated and thanks ahead!
Cheers, dn
Douglas Nichols dnichols@fhcrc.org
---------------------------------------------------------------
National Wilms Tumor Study Group 206.667.4283
Seattle, WA
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] sql question: min remapped value
@ 1999-11-19 00:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Douglas Nichols <dnichols@fhcrc.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 1999-11-19 00:43 UTC (permalink / raw)
To: Douglas Nichols <dnichols@fhcrc.org>; +Cc: pgsql-sql
Douglas Nichols <dnichols@fhcrc.org> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-11-19 00:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-11-18 23:26 sql question: min remapped value Douglas Nichols <dnichols@fhcrc.org>
1999-11-19 00:43 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox