How to select first row in each GROUP BY group?

Sandra Emily 493 15 Jul 2024

Sandra Emily

Student

Content writing is the process of writing, editing, and publishing content in a digital format. That content can include blog posts, video or podcast scripts, ebooks or whitepapers, press releases, product category descriptions, landing page or social media copy and more.

1 Answers

Ashutosh Patel 15 Jul 2024

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

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

Employees Table

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

Let's see the processed,

Using SQL Subquery

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

Example-

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-

Using SQL CTE

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

Example-

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-

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

forum

How to select first row in each GROUP BY group?

Can you answer this question?

1 Answers