Sql server select top 1 from each group năm 2024

Let's say you have tables that contains data about users and sessions, and you want to see the first session for each user for particular day. The function you need here is row_number(). Here's an example of how to use it:

select
  us.user_id,
  us.session_id,
  s.start_date,
  s.end_date,
  row_number() over (partition by user_id order by start_date desc) as row_number
from user_sessions us
left outer join sessions s on s.id = us.session_id
where convert(varchar(10), start_date, 105) = '02-02-2020';

This gives you all the session IDs for the day, plus their row number. Since you only want the first session for the day, you only want rows that have row_number: 1. To do that, you can use a common table expression:

For example, you have a table with data about ‘students’ test scores, and you want to find the highest-scoring student in each subject.

Input

student_id subject score 1 Math 90 2 Math 85 3 Math 95 1 Science 88 2 Science 92 3 Science 89 1 History 78 2 History 88 3 History 92

Try Hands-On: Fiddle

Create Input Table: Gist

Desired Output

student_id subject score 3 History 92 3 Math 95 2 Science 92

There are multiple ways to do this. Let’s look at some of them.

Solution 1:

Using ROW_NUMBER()

In this approach, we consider the Top 1 row are the first row in the table, ordered based on score.

You can use a common table expression (CTE) along with the ROW_NUMBER() function to achieve this. Here’s the SQL query to retrieve the top 1 row from each subject group.

Note: ROW_NUMBER() works on MySQL only from v8 onwards.

    WITH RankedScores AS (
        SELECT
            student_id,
            subject,
            score,
            ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS row_num
        FROM
            test_scores
    )
    SELECT
        student_id,
        subject,
        score
    FROM
        RankedScores
    WHERE
        row_num = 1;

Explanation:

This query first assigns a row number to each row within each subject group, based on the score in descending order. Then, it selects only the rows where the row number is 1, which represents the top-scoring student in each subject.

Solution 2:

By using a subquery with a JOIN

In this approach, we condider the top 1 row to be the row with maximum score.

SELECT t1.student_id, t1.subject, t1.score
FROM test_scores t1
JOIN (
    SELECT subject, MAX(score) AS max_score
    FROM test_scores
    GROUP BY subject
) t2
ON t1.subject = t2.subject AND t1.score = t2.max_score;

Explanation:

This query first creates a subquery (aliased as t2) that calculates the maximum score for each subject group using the MAX() function and GROUP BY.

Then, it joins the original table test_scores with this subquery based on both subject and score.

This way, it retrieves the rows where the score matches the maximum score for each subject group.

Solution 3:

Using a Correlated Subquery

SELECT ts.student_id, ts.subject, ts.score
FROM test_scores ts
WHERE ts.score = (
    SELECT MAX(score)
    FROM test_scores
    WHERE subject = ts.subject
);

Explanation:

In this query, we use a correlated subquery in the WHERE clause.

For each row in the main query (aliased as ts), the subquery finds the maximum score for the same subject in the test_scores table.

If the score of the current row matches the maximum score for that subject, the row is included in the result.

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

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 = 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 

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.

How do you SELECT top 1 of each group in SQL Server?

To select the first row of each group in SQL, you can use the ' GROUP BY ' clause with the ' MIN ' or ' MAX ' aggregate function.

How to get the first record for each group in SQL?

Get the First Row per Group.

To get the first row per group in SQL, you can use the ROW_NUMBER function combined with a PARTITION BY clause. ... .

Replace column_to_group_by with the column you want to group by, and column_to_order_by with the column you want to order the rows by within each group..

How to SELECT top 1 record in each group in Oracle?

To obtain the first record in a group of records, you can use the with clause to create a common table expression including row_number() to obtain the position of each row in the group. Later with an outer query you can filter rows by position.

How to use ROW_NUMBER with GROUP BY in SQL?

To group by row number in SQL, you can use a subquery or a common table expression (CTE). First, assign row numbers to each row using the ROW_NUMBER() function. Then, in the outer query, group the rows by their assigned row numbers.