Getting an aggregate (here: min) for a group of values

Question

Example table:

ID   | value A | value B  
----------------------  
1    |    abc  |   3  
1    |    def  |   5  
1    |    ghi  |   1  
2    |    cba  |   9  
2    |    fed  |   4  

I want the rows from within any 'ID-group' that has it's minimum within that group in value B.

Wanted result:

ID | value A | value B  
----------------------  
1  |    ghi  |   1  
2  |    fed  |   4  

Anything I tried with group by always fails because of that 'value B'-column and its varying values because I do not want to / cannot aggregate them.

Answer 1

Postgres has distinct on that does just what you are looking for:

select distinct on (id) id, value_a, value_b
from t
order by id, value_b;

SQLFiddle here

You haven't specified what you would like to see when the are multiple values of value_b for an id though.

Answer 2

You can do a GROUP BY to get the minimum value of value_B and then join back with the main table. Something like this.

select table1.ID, table1.value_A, table1.value_B 
From table1 inner join 
(select id,min(value_B) value_B from table1 group by ID) t1
on table1.id = t1.id and table1.value_B = t1.value_B;

SQL Fiddle

Answer 3

In PostgreSQL 8.4+ or any other DBMS that supports windowing functions, the following approach will also work, and will probably be faster on large tables because the database can answer the query with a single scan of the main table (rather than aggregating on the first scan and then querying the main table again for matching rows):

select ID, value_A, value_B
from (
  select t.*, row_number() over (partition by ID order by value_B) as r
  from table1 t
) x
where r = 1;

Answer 4

select ID, value_A, value_B 
From t where (value_B,ID) in 
(select min(value_B),ID from t group by ID);