SQL select first row in GROUP [SOLVED]


SQL

Reviewer: Deepak Prasad

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

SQL select first row in GROUP [SOLVED]

 

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 select first row in GROUP [SOLVED]

 

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:

SQL select first row in GROUP [SOLVED]

 

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 by class_id.
  • PARTITION BY clause will create groups of student records based on class_id and each group will contain records in the order of class_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:            

SQL select first row in GROUP [SOLVED]

 

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 by class_id.
  • PARTITION BY clause will create groups of student records based on class_id and each group will contain records in the order of class_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:

SQL select first row in GROUP [SOLVED]

 

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

 

Falguni Thakker

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on her LinkedIn profile.

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment