sql for top 5 records from second table in two joined tables

Question

i have 2 tables. student and student_records.

student table has S_id and has foreign key in student_records.

When joining these tables, I need 1 student ID and top 5 records from student_records table for that Student ID.

1 abc 
1 dedw
..
..
2 def 
2 referf

1 is from Student table and the 'abc','dedw' etc is from student_records Also there is an Active field in the student table. If Active=1 then show top 5 records from student_records else if Active=0 the just have 1 row of the studentid

i hope its clear what i'm trying to say. If not i could explain more. — lkeo, Apr 12 '11 at 21:39

Joe Stefanelli · Answer 1 · 2011-04-12T21:55:56.907

Assuming at least SQL Server 2005:

SELECT S_id, SomeColumn
    FROM (SELECT s.S_id, sr.SomeColumn,
                 ROW_NUMBER() OVER (PARTITION BY s.S_id ORDER BY sr.SomeColumn) AS RowNum
              FROM student s
                  LEFT JOIN student_records sr
                      ON s.S_id = sr.S_id
                          AND s.Active = 1
          ) t
    WHERE t.RowNum <= 5

This could also be written using a CTE.

with cteRowNum as (
    SELECT s.S_id, sr.SomeColumn,
           ROW_NUMBER() OVER (PARTITION BY s.S_id ORDER BY sr.SomeColumn) AS RowNum
        FROM student s
            LEFT JOIN student_records sr
                ON s.S_id = sr.S_id
                    AND s.Active = 1
)
SELECT S_id, SomeColumn
    FROM cteRowNum
    WHERE RowNum <= 5

score 2 · Answer 2 · answered Apr 12 '11 at 21:43

Select ...
From student As S
    Outer Apply (
                Select Top 5 ...
                From student_records As R1
                Where R1.student_id = S.S_id
                    And S.Active = 1
                Order By ??? -- never mentioned in the OP
                Union All
                Select TOP 1 ...
                From student_records As R1
                Where R1.student_id = S.S_id
                    And S.Active = 0
                Order By ??? -- never mentioned in the OP
                ) As R

I used Outer Apply here because I assumed you might have a situation where you have a student with no student_record rows. In addition, you have not specified the rules by which "TOP 5" or "TOP 1" is determined. How should I order the rows in student_records to determine "TOP 5"?

sql for top 5 records from second table in two joined tables

2 Answers2