Get top 1 row of each group sql năm 2024

window function is often used to get the top number of rows in a group. In this article, we’ll look at how to get the top N Row Counts in SQL Server using this function. We’ll provide step-by-step instructions, syntax examples, and examples to help you get the results you need.

ROW_Number() is used in conjunction with PARTITION BY to rank and filter within a group. By using this function, you can get the top n Row Counts based on a specific column, improving the analytical performance of your SQL query.

SELECT Top N Rows For Each Group in SQL Server

The ROW_NUMBER() window function in SQL Server is used to assign unique row numbers to the rows within a partition. Combined with the PARTITION BY clause, it becomes an effective way to rank and filter results within each group.

Steps:

1. Apply the ROW_NUMBER() function to your result set, and specify the PARTITION BY clause to define the grouping based on a specific column.
2. Create a Common Table Expression (CTE) to organize the query and simplify the final selection of rows.
3. Filter the results from the CTE based on the row number and select only the top N rows within each group.

Syntax:

WITH CTE AS ( SELECT

,  
,  
ROW_NUMBER() OVER (PARTITION BY  ORDER BY  DESC) AS row_num  

)
SELECT * FROM CTE WHERE row_num <= N; 
: This represents the column by which you want to group your data. In the context of the example, it could be the Region column as we are grouping by region.
: This represents the column based on which you want to order the data within each group. In the example, it’s Revenue, as we want to rank salespeople within each region by their revenue.
: This represents the table from which you are selecting data. In the example, it’s the Sales table.
Example of SELECT Top N Rows For Each Group in SQL Server
Example 1: Retrieving Top 2 Salespersons per Region
In this example, we have a Sales table with columns Region, Salesperson, and Revenue and we will retrieve the top 2 salespersons based on revenue for each region.
-- Create Sales table
CREATE TABLE Sales ( 
Region VARCHAR(50),  
Salesperson VARCHAR(50),  
Revenue DECIMAL(10, 2)  
);



-- Insert sample data
INSERT INTO Sales (Region, Salesperson, Revenue)
VALUES
('North', 'John', 5000),
('North', 'Alice', 7000),
('North', 'Bob', 6000),
('South', 'Emma', 8000),
('South', 'Chris', 7500),
('South', 'David', 9000);
-- Retrieve top 2 salespersons per region
WITH CTE AS (
SELECT , ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Revenue DESC) AS row_num
FROM 
Sales  
)
SELECT  FROM CTE WHERE row_num <= 2;
Output: 
Output Table with top 2 rows per group
Explanation: The Sales table is created with 6 records and then the ROW_NUMBER() approach is used to retrieve the top 2 salespersons per region. The North and south are the top regions whose top two matched records are printed.
Example 2: Retrieving Latest User Activity
Here you will create a table UserActivity with columns UserID, ActivityType, and Timestamp to retrieve the latest activity for each user.
-- Create UserActivity table
CREATE TABLE UserActivity ( 
UserID INT,  
ActivityType VARCHAR(50),  
Timestamp DATETIME  
);



-- Insert sample data
INSERT INTO UserActivity (UserID, ActivityType, Timestamp)
VALUES
(1, 'Login', '2024-02-14 08:00:00'),
(1, 'UpdateProfile', '2024-02-14 09:30:00'),
(2, 'Login', '2024-02-14 10:00:00'),
(2, 'Logout', '2024-02-14 11:45:00'),
(3, 'Login', '2024-02-14 12:30:00');
-- Retrieve the latest activity for each user
WITH CTE AS (
SELECT , ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY Timestamp DESC) AS row_num
FROM 
UserActivity  
)
SELECT  FROM CTE WHERE row_num = 1;
Output:
Table with top 1 rows per group
Explanation: The UserActivity table is created and then the ROW_NUMBER() approach is used to retrieve the most recent 1 activity for each user. So, every partition is ordered by timestamp in descending order to get the users latest activity when one row is selected from CTE.
Conclusion
The PARTITION BY clause allows you to restrict the results to the first N rows of each group in SQL Server, making your queries more efficient. This way, you can filter and extract only the most important information. Whether you’re looking at sales data or user activity, using this method allows you to make accurate and data-driven decisions quickly and easily.
How do you SELECT the top 1 row from 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 first row of each group in SQL 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 do I get the row number for each row in SQL?
To get row numbers in SQL, you can use the ROW_NUMBER() function. It assigns a unique sequential number to each row in the result set, based on the specified ordering.
How to SELECT top 1 rows in MySQL?
The SQL SELECT TOP Clause.
SQL Server / MS Access Syntax: SELECT TOP number|percent column_name(s) FROM table_name. ... .
MySQL Syntax: SELECT column_name(s) FROM table_name. ... .
Oracle 12 Syntax: SELECT column_name(s) FROM table_name. ... .
Older Oracle Syntax: SELECT column_name(s) ... .
Older Oracle Syntax (with ORDER BY): SELECT *.
Đọc tiếp







mẹo hay
Top List
Top
DISTINCT ON SQL



Reply
8
0
 Chia sẻ