How to select the first row for each group in MySQL?

Question

In C# it would be like this:

table
   .GroupBy(row => row.SomeColumn)
   .Select(group => group
       .OrderBy(row => row.AnotherColumn)
       .First()
   )

Linq-To-Sql translates it to the following T-SQL code:

SELECT [t3].[AnotherColumn], [t3].[SomeColumn]
FROM (
    SELECT [t0].[SomeColumn]
    FROM [Table] AS [t0]
    GROUP BY [t0].[SomeColumn]
    ) AS [t1]
OUTER APPLY (
    SELECT TOP (1) [t2].[AnotherColumn], [t2].[SomeColumn]
    FROM [Table] AS [t2]
    WHERE (([t1].[SomeColumn] IS NULL) AND ([t2].[SomeColumn] IS NULL))
      OR (([t1].[SomeColumn] IS NOT NULL) AND ([t2].[SomeColumn] IS NOT NULL)
        AND ([t1].[SomeColumn] = [t2].[SomeColumn]))
    ORDER BY [t2].[AnotherColumn]
    ) AS [t3]
ORDER BY [t3].[AnotherColumn]

But it is uncompatible with MySQL.

Answer 1

I base my answer in the title of your post only, as I don't know C# and didn't understand the given query. But in mysql I suggest you try subselects. First get a set of primary keys of interesting columns then select data from those rows:

SELECT somecolumn, anothercolumn 
  FROM sometable 
 WHERE id IN (
               SELECT min(id) 
                 FROM sometable 
                GROUP BY somecolumn
             );

Answer 2

You should use some aggregate function to get the value of AnotherColumn that you want. That is, if you want the lowest value of AnotherColumn for each value of SomeColumn (either numerically or lexicographically), you can use:

SELECT SomeColumn, MIN(AnotherColumn)
FROM YourTable
GROUP BY SomeColumn

Some hopefully helpful links:

http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html

http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html

Answer 3

Here's another way you could try, that doesn't need that ID field.

select some_column, min(another_column)
  from i_have_a_table
 group by some_column

Still I agree with lfagundes that you should add some primary key ..

Also beware that by doing this, you cannot (easily) get at the other values is the same row as the resulting some_colum, another_column pair! You'd need lfagundes apprach and a PK to do that!

Answer 4

When I write

SELECT AnotherColumn
FROM Table
GROUP BY SomeColumn
;

It works. IIRC in other RDBMS such statement is impossible, because a column that doesn't belongs to the grouping key is being referenced without any sort of aggregation.

This "quirk" behaves very closely to what I want. So I used it to get the result I wanted:

SELECT * FROM 
(
 SELECT * FROM `table`
 ORDER BY AnotherColumn
) t1
GROUP BY SomeColumn
;