How to select the first row for each group in MySQL?
Posted
by Jader Dias
on Stack Overflow
See other posts from Stack Overflow
or by Jader Dias
Published on 2010-04-29T17:36:27Z
Indexed on
2010/04/29
17:47 UTC
Read the original article
Hit count: 367
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.
© Stack Overflow or respective owner