---
title: "How to select first row in each GROUP BY group?"  
description: "How to select first row in each GROUP BY group?"  
author: "Sandra Emily"  
published: 2024-07-15  
updated: 2024-07-15  
canonical: https://www.mindstick.com/forum/160907/how-to-select-first-row-in-each-group-by-group  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 3 minutes  

---

# How to select first row in each GROUP BY group?

How to [select](https://www.mindstick.com/forum/160534/orderby-then-select-vs-select-then-orderby-performance) first [row](https://www.mindstick.com/forum/1212/this-row-already-belongs-to-this-table) in each [GROUP](https://yourviews.mindstick.com/view/81323/adani-green-energy-group-bags-world-s-biggest-solar-bid) BY group?

## Replies

### Reply by Ashutosh Patel

In SQL Server, you can use the `ROW_NUMBER()` function with a Common Table Expression (CTE) or subquery to achieve this,

Suppose we have two SQL table,

**Departments** Table

```plaintext
CREATE TABLE Departments
(
ID INT NOT NULL PRIMARY KEY IDENTITY(100, 1),
Name NVARCHAR(50) NOT NULL,
Location NVARCHAR(50)
)
```

![How to select first row in each GROUP BY group?](https://www.mindstick.com/mindstickforums/d68b8b02-9f24-4e80-ae52-f4d348e11906/images/110d61e2-6f05-4d4d-b428-a6cae6eeaf32.jpg)

**Employees** Table

```plaintext
CREATE TABLE Employees
(
EmpId INT PRIMARY KEY NOT NULL IDENTITY(100, 1),
EmpName NVARCHAR(50) NOT NULL,
Gender NVARCHAR(20),
Salary DECIMAL(10, 2),
DepartmentId INT FOREIGN KEY REFERENCES Departments(ID)
)
```

![How to select first row in each GROUP BY group?](https://www.mindstick.com/mindstickforums/d68b8b02-9f24-4e80-ae52-f4d348e11906/images/d3823ed9-159a-48c2-abd7-1ff5d85d932d.png)

Let's see the processed,

1. **Using SQL Subquery**

By using SQL, server Subquery you can easily select the first records from each grouped items.

## Example-

```plaintext
SELECT EmpId, EmpName, Gender, Salary, Department  FROM (
	SELECT E.EmpId, E.EmpName, E.Gender, E.Salary,D.Name AS Department,
	ROW_NUMBER() OVER(PARTITION BY D.Name ORDER BY D.Name) AS RowNum
	FROM Employees E INNER JOIN Departments D ON D.ID = E.DepartmentId
) AS GroupedData WHERE RowNum = 1
```

Output-

![How to select first row in each GROUP BY group?](https://www.mindstick.com/mindstickforums/d68b8b02-9f24-4e80-ae52-f4d348e11906/images/087e7cf1-574e-4a29-9584-999af5c09f18.png)

## Using SQL CTE

You can also achieve the same result by using the SQL CTE (Common Table Expression)

## Example-

```plaintext
USE MyCollegeDb
GO
WITH CTE AS (
SELECT E.EmpId, E.EmpName, E.Gender, E.Salary,D.Name AS Department,
ROW_NUMBER() OVER(PARTITION BY D.Name ORDER BY D.Name) AS RowNum
FROM Employees E INNER JOIN Departments D ON D.ID = E.DepartmentId
)
SELECT EmpId, EmpName, Gender, Salary, Department FROM CTE WHERE RowNum =1
```

## Output-

![How to select first row in each GROUP BY group?](https://www.mindstick.com/mindstickforums/d68b8b02-9f24-4e80-ae52-f4d348e11906/images/4c5cdd65-289e-47cd-b45a-642bca1ebbae.png)

## In the example above-

`ROW_NUMBER()` This function assigns a unique sequence of integers to each row in the partition of the result set defined by the `PARTITION BY` clause. The ORDER BY clause in `ROW_NUMBER()` determines the order in which the numbers are assigned.

`PARTITION BY` Specifies how to divide the result set into partitions. Rows with the same values ​​in YourGroupColumn will belong to the same partition.

`ORDER BY` Defines the order of the rows in each partition. SomeColumn is often used here to refer to the first row.

If you apply condition `RowNum = 1`, you are effectively selecting only the first row in each group defined by `D.Name` column.

**Also, Read:** [How to concatenate text from multiple rows into a single text string in SQL Server](https://www.mindstick.com/forum/160908/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-server)


---

Original Source: https://www.mindstick.com/forum/160907/how-to-select-first-row-in-each-group-by-group

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