CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Vijay
Vijay

Reputation: 55

select top 3 from table

I have three tables.

Table 1:

Student
----------
studentID

StudentName

Table 2:

Studentmarks

----------------
studentID

subjectId

marks

Table 3:

Subject
---------
subjectID

subjectName

I need a query to get top 3 students in each subject.

I want output like:

----------------------------------------------------------------
    StudentID         Name    subjectId    subjectName    marks
-----------------------------------------------------------------
       1               AAA        1           phy        85
       2               BBB        1           phy        75
       3               CCC        1           phy        65
       3               CCC        2           bio        85
       4               DDD        2           bio        75
       1               AAA        2           bio        65
       6               FFF        3           che        85
       1               AAA        3           che        75
       5               EEE        3           che        65
       3               CCC        4           mat        85
       2               BBB        4           mat        75
       4               DDD        4           mat        65

Upvotes: 1

Views: 183

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

WITH CTE
AS
(
   SELECT
     s.StudentID,
     s.StudentName,
     sm.subjectId,
     j.SubjectName,
     sm.marks,
     ROW_NUMBER() OVER(PARTITION BY sm.subjectId 
                       ORDER BY sm.marks DESC) AS RN
   FROM student AS s
   INNER JOIN Studentmarks AS sm ON s.studentID  = sm.studentID
   INNER JOIN Subject      AS j  ON sm.subjectId = j.SubjectId
)
SELECT
  StudentID,
  StudentName,
  subjectId,
  SubjecTName,
  marks
FROM CTE
WHERE RN <= 1;

Upvotes: 4

Related Questions