To get Top (N) row with Group By clause

Neeks
Posted by Neeks under Sql Server category on | Views : 3735
There may be case that you need to use group by option and number of row per group then you need to use this query.
For example you want to find three students from all the cities in your database. In this case you have to use the following query.

This will return you top two rows for each city
WITH student_grp AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY cityid ORDER BY cityid DESC) AS 'RowNo',
cityid, FName, LName FROM student
)
SELECT cityid, FName, LName FROM student_grp
WHERE RowNo <=2

Comments or Responses

Login to post response