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 

Answer 2

As you can see there is a solution that works using only standard SQL, but it's long and complicated.

Note that it's also possible to write a "Hello, world!" program that works correctly in three different programming languages. Doing this doesn't usually add any value to your program though. It's much easier just to write the program three times, tailored to the specific syntax of each language.

I think with SQL it is often better to forget trying to find a solution that works in all RDBMS and use the specific vendor extensions and idioms that make queries like this easier. For example in MS SQL Server, you could do something like this:

SELECT Field1, Field2, Field3
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY field2) AS rn
    FROM table1
) AS T1
WHERE rn = 1

Anyway, you already have a general solution, so I just thought I'd offer this alternative viewpoint.