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.

      ID INT,
customer_name VARCHAR(100),
order_date DATETIME,
total_orders INT
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

FROM orders o
      -- 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;

# 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:

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
      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’.

# 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:

Read How to avoid SQL injection in PHP?