---
title: "How to Write a Query to Get Top N Records per Group in SQL Server?"  
description: "How to Write a Query to Get Top N Records per Group in SQL Server?"  
author: "Anubhav Sharma"  
published: 2024-07-15  
updated: 2024-07-16  
canonical: https://www.mindstick.com/forum/160912/how-to-write-a-query-to-get-top-n-records-per-group-in-sql-server  
category: "SQL Server"  
tags: ["database", "sql server", "sql server 2008", "sql server 2012", "sql server 2022"]  
reading_time: 2 minutes  

---

# How to Write a Query to Get Top N Records per Group in SQL Server?

Hello,

I want to get the top 3 highest-paid [employees](https://www.mindstick.com/articles/44463/business-to-business-vat-reclaiming-a-guide-for-your-employees) in each department from the `Employee` table. How can I write this [query in SQL](https://www.mindstick.com/forum/160921/help-with-writing-a-recursive-query-in-sql-server) Server?

## Replies

### Reply by Ravi Vishwakarma

To get the **top 3 highest-paid employees** in each department from the `Employee` table in SQL Server, you can use the `ROW_NUMBER()` window function along with a [**Common Table Expression (CTE)**](https://www.mindstick.com/blog/304466/how-do-i-use-cte-to-simplify-complex-queries-in-sql-server) or a [**subquery**](https://www.mindstick.com/forum/160566/what-is-the-order-of-execution-process-of-subquery-in-sql).

Here is an example [query](https://www.mindstick.com/blog/202/sub-query-in-sqlserver):

#### Using CTE

```plaintext
WITH RankedEmployee AS (
	select
		*,
		ROW_NUMBER() OVER(PARTITION BY DepartMentID Order BY Salary DESC) AS RowNum
	from Employees
)
select * from RankedEmployee
WHERE
    RowNum <= 3
```

## Explanation:

1. **CTE Definition**: The `WITH RankedEmployees AS (...)` part defines a Common Table Expression (CTE) named `RankedEmployees`.
2. **ROW_NUMBER() Function**: Inside the CTE, the `ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC)` assigns a unique rank to each employee within their department, ordered by salary in descending order.
3. **Selecting Top 3**: The `SELECT` statement outside the CTE filters the results to include only those rows where `RowNum` is less than or equal to 3, effectively selecting the top 3 highest-paid employees in each department.

#### Using Subquery

Alternatively, you can achieve the same result using a subquery:

```plaintext
SELECT
    *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowNum
    FROM
        Employees
) AS RankedEmployees
WHERE
    RowNum <= 3
```

## Read more

[**Help with Writing a Subquery to Get Aggregate Data in SQL Server**](https://www.mindstick.com/forum/160910/help-with-writing-a-subquery-to-get-aggregate-data-in-sql-server)

[**What is the order of execution process of subquery in SQL?**](https://www.mindstick.com/forum/160566/what-is-the-order-of-execution-process-of-subquery-in-sql)

[**Can we use ORDER BY in subquery in SQL?**](https://www.mindstick.com/forum/160565/can-we-use-order-by-in-subquery-in-sql)

[**Explain the difference between a subquery and a join in SQL Server.**](https://www.mindstick.com/forum/158355/explain-the-difference-between-a-subquery-and-a-join-in-sql-server)


---

Original Source: https://www.mindstick.com/forum/160912/how-to-write-a-query-to-get-top-n-records-per-group-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.