How to Select the First Row of Each Group in MySQL?

How to Select the First Row for Each Group in MySQL

When working with data in MySQL, it's often necessary to retrieve only the first row of each group based on specific criteria. While Linq-To-Sql provides a convenient way to accomplish this in C# using the GroupBy and Select methods, the resulting T-SQL code is incompatible with MySQL.

MySQL offers a simple and efficient way to select the first row for each group using subselects. Here's how it can be done:

1. Create a subquery to retrieve the primary keys of the desired groups. Use the GROUP BY clause to group the rows and then use the MIN() aggregation function to retrieve the minimum primary key value for each group.

SELECT MIN(id) AS group_id
FROM sometable
GROUP BY somecolumn

2. Use the subquery as a condition in the main query to select the actual data. Filter the rows in the main query using the group IDs obtained from the subquery.

SELECT somecolumn, anothercolumn 
FROM sometable 
WHERE id IN (
    SELECT group_id
    FROM (
        SELECT MIN(id) AS group_id
        FROM sometable
        GROUP BY somecolumn
    ) AS subquery
);

This query will effectively retrieve the first row for each group based on the somecolumn column, ensuring the data is organized and easy to process.

The above is the detailed content of How to Select the First Row of Each Group in MySQL?. For more information, please follow other related articles on the PHP Chinese website!