SQL FAQ Feat

How to select the first row of each GROUP BY in SQL?

  • How-Tos FAQs
  • December 16, 2018

Let us create a table ‘orders’ to replicate the problem.

CREATE TABLE orders (
      ID INT,
customer_name VARCHAR(100),
order_date DATETIME,
total_orders INT
);
INSERT INTO  orders
SELECT 1,'Jack', '2020-02-03', 4 UNION ALL
SELECT 2, 'Rose', '2020-01-09', 19 UNION ALL
SELECT 3,'Jack', '2020-02-03', 40 UNION ALL
SELECT 4, 'Rose', '2020-01-09', 21 ;
 

There are a couple of ways to get the first row for each GROUP BY in Mysql:

1.  Using a subquery

SELECT o.*
FROM orders o
INNER JOIN (
      -- Replace MAX with MIN if orders with minimum order needs to be selected
      SELECT customer_name, MAX(total_orders) total_orders
      FROM orders                        
      GROUP BY customer_name
) sub
ON o.customer_name = sub.customer_name
          AND o.total_orders = sub.total_orders;

#output
# ID    customer_name   order_date                  total_orders
-------------------------------------------------------------------
3       Jack            2020-02-03 00:00:00         40
4       Rose            2020-01-09 00:00:00         21

But if there are data like below where there are multiple rows with maximum total orders:

INSERT INTO orders
SELECT 5,'Angel', '2020-02-03', 4 UNION ALL
SELECT 6, 'Angel', '2020-01-09', 19 UNION ALL
SELECT 7, 'Angel', '2020-11-05', 19;

The above query will return two rows for customer “Angle.” To solve this issue, we can use the window function as below:

2.  Using Window function

SELECT id, customer_name, order_date,total_orders
FROM (
      SELECT id
            , customer_name
            , order_date
            , total_orders
            , ROW_NUMBER() OVER(PARTITION BY customer_name ORDER BY total_orders desc)row_num
      FROM orders
    ) sub
WHERE row_num = 1

This approach returns only 1 row even when there are multiple maximum rows for customer ‘Angle’.

#output
# id       customer_name  order_date            total_orders
-------------------------------------------------------------------------------
6          Angel          2020-01-09 00:00:00    19
3          Jack              2020-02-03 00:00:00    40
4          Rose              2020-01-09 00:00:00    21 

Up Next:

Read How to avoid SQL injection in PHP?

More Resources We Think You Might Like

SQL FAQ Feat

How to return all information of a row with MAX...

Suppose we have a table “score” as below, and we need all information on a row with the maximum s...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

How to concatenate data from different rows of ...

Let us consider the following table: CREATE TABLE fruits (     ID INT,     NAME VARCHAR(50) ) INS...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

How to insert the result of a stored procedure ...

Let’s create a simple stored procedure (SP) with a SELECT query. There are different ways to inse...

  • How-Tos FAQs
  • December 16, 2018