Finding TOP X records from each group SQL

Create a table named photo_test and insert some test data as :-

 Collapse

create table photo_test

(

pgm_main_Category_id int,

pgm_sub_category_id int,

file_path varchar(MAX)

)

insert into photo_test values

(17,15,'photo/bb1.jpg');     

insert into photo_test values(17,16,'photo/cricket1.jpg');                                                    

insert into photo_test values(17,17,'photo/base1.jpg');                                                       

insert into photo_test values(18,18,'photo/forest1.jpg');                                                       

insert into photo_test values(18,19,'photo/tree1.jpg');                                                           

insert into photo_test values(18,20,'photo/flower1.jpg');                                                     

insert into photo_test values(19,21,'photo/laptop1.jpg');                                                       

insert into photo_test values(19,22,'photo/camer1.jpg');                                                 

insert into photo_test values(19,23,'photo/cybermbl1.jpg');                                                    

insert into photo_test values

(17,24,'photo/F1.jpg');

There are three groups of pgm_main_category_id each with a value of 17 (group 17 has four records),18 (group 18 has three records) and 19 (group 19 has three records). 
Now, if you want to select top 2 records from each group, the query is as follows:-

 Collapse

select pgm_main_category_id,pgm_sub_category_id,file_path from

(

select pgm_main_category_id,pgm_sub_category_id,file_path,

rank() over (partition by pgm_main_category_id order by pgm_sub_category_id asc) as rankid

from photo_test

) photo_test

where rankid < 3 -- replace 3 by any number 2,3 etc for top2 or top3.

order by pgm_main_category_id,pgm_sub_category_id

The result is as:-

 Collapse

pgm_main_category_id    pgm_sub_category_id      file_path

17                       15                      photo/bb1.jpg

17                       16                      photo/cricket1.jpg

18                       18                      photo/forest1.jpg

18                       19                      photo/tree1.jpg

19                       21                      photo/laptop1.jpg

19                       22                      photocamer1.jpg