Обсуждение: sql question: min remapped value

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

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