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 21But 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 = 1This 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