How do I group by min value in one field of table, keeping all the values from that same row?

Question

I have a table like this:

Field1 | Field2 | Field3
1      | 1      | 22
1      | 2      | 10
2      | 5      | 40
2      | 2      | 55

I want to group by Field1, and then take the values from the rest of the row with the minimal Field2, eg:

Field1 | Field2 | Field3
1      | 1      | 22
2      | 2      | 55

Note that this is not the same as selecting the minimum of each row, which would give:

Field1 | Field2 | Field3
1      | 1      | 10
2      | 2      | 40

Which with my data would be a meaningless result.

Does anyone have a general (ie. multi database) solution to this? I'm sure it must be a solved problem!

I could really do with a solution that works in both sqlite and ms-access, and sql server would be a bonus.

Answer 1

You'll have to execute this with subqueries.

Select * from 
 yourtable t
  inner join 
    (   Select field1, min(field2) as minField2 
        from yourtable 
        group by field1
     ) xx 
    on t.field1 = xx.field1 and t.field2 = xx.minfield2

Now, if you have multiple rows for a minimal field2 value, then you'll have dupes...If you don't want that (i.e. you want the minimal value of field3 for every minimal value of field2) in that case, you'd need another sub query:

Select outert.field1, outert.field2, outert.field3
from yourtable outert 
inner join 

( 
 Select t.field1, xx.minfield2, min(field3) as minfield3 from 
 yourtable t
  inner join 
    (   Select field1, min(field2) as minField2 
        from yourtable 
        group by field1
     ) xx 
    on t.field1 = xx.field1 and t.field2 = xx.minfield2
 group by t.field1, xx.minfield2
) outerx
on outerx.field1 = outert.field1 
and outerx.field2 = outert.minfield2
and outerx.field3 = outert.minfield3