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 incompatible with MySQL.

Answer 1

I based my answer on 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              );