Home > Database > Mysql Tutorial > How to Select the First Row of Each Group in MySQL?

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

Linda Hamilton
Release: 2024-11-04 19:54:02
Original
487 people have browsed it

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
Copy after login
  1. 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
);
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template