none
Retrieve TOP 5 records, by GROUP RRS feed

  • Question

  • I want to retrieve the TOP 5 records from a dataset, but I need them grouped by student ID.

    Currently I’m running my script with a SELECT TOP 5 * from myTable GROUP BY studentID, but it only retrieve 5 rows in total.

    Can anyone assist please?

    Tuesday, October 2, 2012 1:55 PM

Answers

  • Sam,

    Use a ROW_NUMBER() function for this; partition by Student ID; order by @@spid or (select 0) if you have nothing else to order by, but you should order by something "real" if it is available.  Perhaps something like this:

    declare @test table
    ( student_Id integer,
      class varchar(20));
    insert into @test
    select 1, 'English 0001' union all select 1, 'English 0000' union all
    select 1, 'Math 0101' union all select 1, 'Chem 0101' union all
    select 1, 'Phys 0102' union all select 1, 'German 0102' union all
    select 2, 'Basket 0002'
    ;
    with listCte as
    ( select *, 
        row_Number() over
    	( partition by student_Id
    	  order by @@spid
    	) as Rn
      from @test
    )
    select *
    from listCte
    where rn <= 5;
    /* -------- Output: --------
    student_Id  class                Rn
    ----------- -------------------- --------------------
    1           English 0001         1
    1           English 0000         2
    1           Math 0101            3
    1           Chem 0101            4
    1           Phys 0102            5
    2           Basket 0002          1
    */


    Tuesday, October 2, 2012 1:59 PM
    Moderator
  • It's a bit vague, but try something like:

    ;with cte as
    (
    	Select	*
    			,ROW_NUMBER() OVER(Partition By StudentId Order By StudentId) RowNumber
    	From	myTable
    )
    Select	*
    From	cte
    Where	RowNumber <= 5
    Group By StudentId

    Tuesday, October 2, 2012 1:59 PM

All replies

  • Sam,

    Use a ROW_NUMBER() function for this; partition by Student ID; order by @@spid or (select 0) if you have nothing else to order by, but you should order by something "real" if it is available.  Perhaps something like this:

    declare @test table
    ( student_Id integer,
      class varchar(20));
    insert into @test
    select 1, 'English 0001' union all select 1, 'English 0000' union all
    select 1, 'Math 0101' union all select 1, 'Chem 0101' union all
    select 1, 'Phys 0102' union all select 1, 'German 0102' union all
    select 2, 'Basket 0002'
    ;
    with listCte as
    ( select *, 
        row_Number() over
    	( partition by student_Id
    	  order by @@spid
    	) as Rn
      from @test
    )
    select *
    from listCte
    where rn <= 5;
    /* -------- Output: --------
    student_Id  class                Rn
    ----------- -------------------- --------------------
    1           English 0001         1
    1           English 0000         2
    1           Math 0101            3
    1           Chem 0101            4
    1           Phys 0102            5
    2           Basket 0002          1
    */


    Tuesday, October 2, 2012 1:59 PM
    Moderator
  • It's a bit vague, but try something like:

    ;with cte as
    (
    	Select	*
    			,ROW_NUMBER() OVER(Partition By StudentId Order By StudentId) RowNumber
    	From	myTable
    )
    Select	*
    From	cte
    Where	RowNumber <= 5
    Group By StudentId

    Tuesday, October 2, 2012 1:59 PM