1

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.

0
select ID, value_A, value_B 
From t where (value_B,ID) in 
(select min(value_B),ID from t group by ID);
  • 2
    I'm afraid this is not correct. If the first line was 1 | abc | 4, then it would appear in the results of this query. – ypercubeᵀᴹ May 15 '15 at 9:59
  • sorry mate I didn't get you. It will not get selected since for ID=1 minimum value of VALUE_B is still 1. Correct me if I am wrong – vipin May 15 '15 at 10:02
  • 1
    No worries. You can either delete the answer or correct it. – ypercubeᵀᴹ May 15 '15 at 10:08
  • 1
    Why are you concatenating the columns as strings? Most (all?) DBMSes should support ... where (ID, value_B) in (select ID, min(value_B) ... – Colin 't Hart May 15 '15 at 10:17
  • 1
    @user66529 You can accept any one of the answers as "most helpful". This marks your question solved. But please edit the question ans state the DBMS you are using (mySQL, Postgres, Oracle, ...). – ypercubeᵀᴹ May 15 '15 at 10:50
3

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.

  • I always forget about this very handy distinct on syntax that PostgreSQL has! Coming from an Oracle background, I always solve these type of queries using window functions; using rank() or dense_rank() in the query of my answer will solve the last point: what to do with multiple values. – Colin 't Hart May 15 '15 at 11:15
  • 1
    It doesn't solve the problem that the OP hasn't specified how he wants ties broken though ;) – Jack Douglas May 15 '15 at 13:44
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

  • Thanks for quick answer. I was too lazy to try as the other answer was shorter to type... Guess your code should have done it too! – Gerd May 15 '15 at 10:49
  • Not an issue. As long your question gets answered, it's all good. – ughai May 15 '15 at 11:04
2

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;

Not the answer you're looking for? Browse other questions tagged or ask your own question.