select first N number of records per group using ROW_NUMBER() in MSSQL

Here i have taken 2 tables called DeptMaster and TeacherMaster

id    DeptDesc
----------------------
1      BCA
2      BCOM
3      BCOM-CA
4      BSC
5      MCA

id      Name         Deptid       Salary
--------------------------------------------------
1      Pritam Das      1         20000.00
2      Nilima sen       2         18000.00
3      Lisa Das          1         30000.00
4      Rina Das         1         25000.00
5      Yubaraj S        2         16000.00
6      Arabind S        3         17000.00
7      Raja M            3         15000.00
8      Hari A              1         20000.00
9      Mala K             3        30000.00
10    Azad S             1         27000.00
11    Manu P            2         25000.00

First we have to make group by and create a ROW_NUMBER over each group.

select Name,DeptDesc,salary from (
select Name,Deptid,salary,
ROW_NUMBER() over (partition by Deptid Order by salary desc) as Rownum
from [dbo].[TeacherMaster] ) t
inner join [dbo].[DeptMaster] DM ON t.deptid=DM.id
where Rownum < N+1

Here i am using ‘Order by salary desc‘ to getting highest salary.
Please look on Inner join part to get more idea on this query.

Output

Name        DeptDesc        Salary
——————————————-
Lisa Das    BCA            30000.00
Azad S       BCA            27000.00
Manu P      BCOM         25000.00
Nilima sen  BCOM         18000.00
Mala K       BCOM-CA    30000.00
Arabind S   BCOM-CA    17000.00