In this tutorial we will cover different methods and examples to select first row in GROUP BY clause group in SQL. But before that let us create a sample table which we will use through out the article in the examples.
Lab Environment
Consider student table of student result management system to perform practical examples on select first row of each group
Introduction to SQL GROUP BY clause
SQL GROUP BY clause creates groups of same value of specified column. To retrieve first record from each group we need to use SQL ROW_NUMBER() function which will assign row number to each groups. Each groups row number will starts with one.
SQL GROUP BY clause is used to group records with the same value in a particular column. It is used to arrange identical data into groups. SQL GROUP BY clause is used with SQL select statements to divide the query results into groups of rows, usually by performing one or more aggregations on each group. The SELECT statement returns one row per group.
SELECT column1,column2,column3,..
FROM table_name
WHERE condition
GROUP BY column1,column2,column3,..
ORDER BY column1,column2,column3,..;
Write SQL query count total number of students city wise
SELECT COUNT(student_id) AS 'Total student', city
FROM tblstudent
GROUP BY city;
OUTPUT:
SQL ROW_NUMBER() function with PARTITION BY clause
SQL row_number ()
function provides consecutive numbering of the rows in the result for each partition specified in the OVER clause. It will assign the value one to the first row and increase it for each successive row.
Syntax
ROW_NUMBER ()
OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)
Example-1: Select first row in each GROUP BY group using ROW_NUMBER() function
Write SQL query to retrieve first record each city student group
WITH groups as (select student_id,studentname,city,ROW_NUMBER() over
(PARTITION BY city order by city) as [ROW NUMBER] from tblstudent)
select * from groups where groups.[ROW NUMBER] =1;
- In the above query, we have used nested select query the nested SQL select statement with
ROW_NUMBER()
function is applied to retrieve student records partition by city. - PARTITION BY clause will create groups of student records based on city name and each group will contain records in the order of city name.
- Each group of records will store in groups table
- To retrieve first row from each group the outer SQL select statement is applied with condition as
ROW_NUMBER=1
.
OUTPUT:
Example-2: Select first row in each GROUP BY group using ROW_NUMBER() function and Inner join
Write SQL query to retrieve first row of each group of student records partition by class_id
in the sorting order of class_id
WITH groups as (select tblstudent.student_id,studentname,class_id,city,examname,ROW_NUMBER() over
(PARTITION BY class_id order by class_id) as [ROW NUMBER] from tblstudent,tblresult
where tblstudent.student_id =tblresult.student_id)
select * from groups where groups.[ROW NUMBER] =1;
- In the above query, we have used nested select inner join query the nested SQL select statement with
ROW_NUMBER()
function is applied to create groups of student records partition byclass_id
. - PARTITION BY clause will create groups of student records based on
class_id
and each group will contain records in the order ofclass_id
and row number starts with 1. - Each group of records will store in groups table
- To retrieve first row from each group the outer SQL select statement is applied with condition as
ROW_NUMBER=1
.
OUTPUT:
Example-3: Select first row in each GROUP BY group using ROW_NUMBER() function , Inner join Where Condition
IN SQL we can also retrieve first row of each group of group by with condition. As we have discuss in previous examples ROW_NUMBER()
function with PARTITION BY and OVER clause is used to assign row number to each group and we are using where condition to check for row number as 1. We can also applied more than one condition in the where clause using logical operator.
Write SQL query to retrieve first row of each group of student records partition by class_id
in the sorting order of class_id
whose are living in ‘Surat’ city.
WITH groups as (select tblstudent.student_id,studentname,class_id,city,examname,ROW_NUMBER() over
(PARTITION BY class_id order by class_id) as [ROW NUMBER] from tblstudent,tblresult where tblstudent.student_id =tblresult.student_id)
select * from groups where groups.[ROW NUMBER] =1 and city='Surat';
- In the above query, we have used nested select inner join query the nested SQL select statement with
ROW_NUMBER()
function is applied to create groups of student records who are living in ‘Surat’ city partition byclass_id
. - PARTITION BY clause will create groups of student records based on
class_id
and each group will contain records in the order ofclass_id
and row number starts with 1. - Each group rows will store in groups table
- To retrieve first row from each group the outer SQL select statement is applied with condition as
ROW_NUMBER=1
.
OUTPUT:
Summary
In this article get first row of each group SQL, we have covered how to select first row in each group of records in SQL, overview of SQL GROUP BY clause, syntax and example of SQL GROUP BY clause , SQL ROW_NUMBER()
function with PARTITION BY clause, syntax of SQL ROW_NUMBER()
function with PARTITION BY clause and also explain examples of select first row in each GROUP BY group using ROW_NUMBER()
function, Select first row in each GROUP BY group using ROW_NUMBER()
function with Inner join and where condition.
References
SQL GROUP BY
SQL ROW NUMBER() function
Read More
learn.microsoft.com: ROW_NUMBER
Select first row in each GROUP BY group? - sql - Stack Overflow