Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

Home Forums How to select first row in each GROUP BY group? – MindStick
Ask Question

1 Answers

Ashutosh Patel
Ashutosh Patel 15 Jul 2024
Report Abuse

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

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

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-

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-

Ashutosh Patel

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-

Ashutosh Patel

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

MindStick Training
Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md