How to SELECT Top N Rows For Each Group in SQL Server
Last Updated :
20 Feb, 2024
SQL Server’s ROW_Number() 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:
- Apply the ROW_NUMBER() function to your result set, and specify the PARTITION BY clause to define the grouping based on a specific column.
- Create a Common Table Expression (CTE) to organize the query and simplify the final selection of rows.
- 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
<group_column>,
<value_column>,
ROW_NUMBER() OVER (PARTITION BY <group_column> ORDER BY <value_column> DESC) AS row_num
FROM
<Table>
)
SELECT * FROM CTE WHERE row_num <= N;
- <group_column>: 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.
- <value_column>: 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.
- <Table>: 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 groupExplanation: 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 groupExplanation: 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.
Similar Reads
How to Select the First Row of Each GROUP BY in SQL?
Selecting the first row of each group in SQL is a common requirement in database queries, especially when working with grouped data. SQL GROUP BY queries are highly effective for organizing data into logical groups, and additional techniques like window functions help to isolate the first row from e
5 min read
How to Retrieving the Last Record in Each Group in SQL Server
In SQL Server data analysis, the need to retrieve the last record in each group is a common and essential task. Consider a scenario where you are managing a database of customer orders. Each order is associated with a unique order ID and a customer ID, and you want to identify the most recent order
4 min read
How to Select Group of Rows that Match All Items on a List in SQL Server?
In this article, we will see, how to select a group of rows that match all the items on a list in SQL Server. We can perform the above function by two methods. Those are: By using IN operatorBY performing JOIN using STRING_SPLIT methodIN operator: It allows you to specify a list of values in a WHERE
2 min read
How to Select the nth Row in a SQL Server Database Table?
In SQL Server databases, it's common to encounter scenarios where we need to retrieve a specific row, such as the nth row, from a table efficiently. Whether you're building a pagination feature for a web application or analyzing data, having the ability to select a particular row based on its positi
3 min read
How to Join to First Row in SQL Server
Joining the first row in SQL Server can be a common requirement in various scenarios, such as when we need to retrieve specific data associated with the first occurrence of a particular group or when you want to fetch additional details related to the first record in a result set. In this article, W
4 min read
How to Select Row With Max Value in SQL Server
In SQL Server, retrieving rows that contain the maximum value for a specific column for each distinct value in another column can be a common and challenging task. This process is done by identifying the maximum value for each group and then selecting the corresponding rows. In this article, we'll e
6 min read
How to Select the Top 10 Rows From a Table in MariaDB
In the data managing systems, MariaDB stands as a robust and adaptable MariaDB stands as a robust and adaptable choice in data managing systems. It's known for efficiently retrieving data with lots of tools and instructions. We're looking at the SELECT TOP clause, a key part of fetching data from
5 min read
How to Restrict Results to top N Rows per Group in PL/SQL?
In the world of database administration, retrieving top N rows from every grouping is a frequent but complicated one. Whether you are performing cross-tabulations with large datasets or looking for specific insights within the grouping of data, the ability to restrict the output to top N rows per gr
4 min read
How To Get Last Record In Each Group In MySQL?
In MySQL, we group the data using the GROUP BY clause. There can be a need to access these groups and get the latest record in each group, for example, tracking the last login timestamp for each user. Knowing how to retrieve the latest record from a group is essential, as it is used in many practica
4 min read
How to Limit Rows in a SQL Server?
To limit rows in SQL Server, use the TOP clause in the SELECT statement. Using the TOP clause in SQL Server, users can limit the number of rows in the results set. Here, we will understand how to limit rows in SQL Server with the help of different examples. Steps to Limit Rows in SQL ServerLet's che
3 min read