How to select the first row of each GROUP BY in SQL in Datameer
- How-Tos FAQs
- December 16, 2018
In order to select the first row of each GROUP BY in SQL, 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 :
Method 1 – Leveraging Datameer’s Zero-Code Capabilities.
With Datameer, you can select the first row of each ‘group by’ in SQL without writing a single line of code. Datameer has various interfaces, making it the perfect tool for all types of users.
It offers a ‘No-code interface’ which is entirely graphical and drag-and-drop for non-technical data and business analysts.
Watch the video below, to see Datameer perform the group by of our orders table using absolutely zero code.
Amazing right?
If that piqued your interest and you would like to explore further, Click the below to sign up for a trial account!
Method 2 – Using a subquery in MYSQL
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:
Method 3 – Leveraging the Window function in MySQL
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 So that’s how we can select the first row of each GROUP BY in SQL
If you’d like to get insights from your data within minutes, feel free to take Datameer for a spin and try it out yourself.
Up Next: