sql - In PostgreSQL how can I return the entire row that corresponds with the min of a value? -

so if have table this

id | value | detail ------------------- 12 | 20    | orange 12 | 30    | orange 13 | 16    | purple 14 | 50    | red 12 | 60    | blue 

how can return this?

12 | 20 | orange 13 | 16 | purple 14 | 50 | red 

if group id , detail returns both 12 | 20 | orange , 12 | 60 | blue

sql fiddle

postgresql 9.3 schema setup:

create table test( id int, value int, detail varchar ); insert test values ( 12, 20, 'orange' ); insert test values ( 12, 30, 'orange' ); insert test values ( 13, 16, 'purple' ); insert test values ( 14, 50, 'red' ); insert test values ( 12, 60, 'blue' ); 

query 1:

not sure if redshift supports syntax:

select distinct        first_value( id ) on wnd id,        first_value( value ) on wnd value,        first_value( detail ) on wnd detail   test window wnd ( partition id order value ) 

results:

| id | value | detail | |----|-------|--------| | 12 |    20 | orange | | 14 |    50 |    red | | 13 |    16 | purple | 

query 2:

select t.id,        t.value,        t.detail (   select *,          row_number() on ( partition id order value ) rn     test ) t  t.rn = 1 

results:

| id | value | detail | |----|-------|--------| | 12 |    20 | orange | | 13 |    16 | purple | | 14 |    50 |    red |