How to Get the First Row per Group in MySQL

In SQL, there are scenarios where you want to select the first row for each group in a dataset. This is commonly required when dealing with reports or summarizing data by specific categories. In this tutorial, we will explore different ways to achieve this in MySQL.

Problem Scenario

Let's say you have a table of Orders with the following columns:

• OrderID
• CustomerID
• OrderDate

You need to retrieve the first order placed by each customer based on the order date. The result should give the first row (order) for each customer, ordered by the order date.

Solution Using Subquery

One approach to get the first row per group is by using a subquery that selects the minimum OrderDate for each CustomerID:

SELECT o.*
FROM Orders o
WHERE o.OrderDate = (
    SELECT MIN(OrderDate)
    FROM Orders
    WHERE CustomerID = o.CustomerID
);
                                

This query works by first selecting the minimum order date for each customer and then retrieving the orders that match those dates.

Using the ROW_NUMBER() Window Function (MySQL 8.0+)

With MySQL 8.0 and later, we can use the ROW_NUMBER() window function to efficiently retrieve the first row per group. The query below assigns a row number to each order per customer based on the order date, then filters the result to only include the first row for each customer:

WITH RankedOrders AS (
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.CustomerID ORDER BY o.OrderDate) AS row_num
    FROM Orders o
)
SELECT *
FROM RankedOrders
WHERE row_num = 1;
                                

In this query, ROW_NUMBER() generates a unique row number for each row within the partitioned groups of customers, sorted by order date. We then filter for the row where row_num equals 1 to get the first order for each customer.

Conclusion

In this tutorial, we've demonstrated two ways to get the first row per group in MySQL: using a subquery and the ROW_NUMBER() window function. The window function method is generally more efficient and is preferred for MySQL 8.0 and later. Choose the method that best fits your version of MySQL and the specific needs of your query.