Try to search your question here, if you can't find : Ask Any Question Now ?

Finding top 3 students from the Student table and Department table group by department

HomeCategory: stackoverflowFinding top 3 students from the Student table and Department table group by department
Avatarcraig asked 2 weeks ago

Suppose we have two Tables:
1. Student
2. Department

  Student table has 4 columns 
   1. id (int pk)
   2. name (varchar)
   3. percentage (int)
   4. dept_id (fk)

  Department table has 3 columns
   1. dept_id (int pk)
   2. dept_name (varchar)   

Query is to select top 3 students from the each department having highest percentage.

I have already written the query with row_num() function.

But, I am facing problem when there are students with the same percentage.

Student table with:
id  name   percentage  dept_id
1   a      70          1
2   b      80          1 
3   c      90          1 
4   d      70          1
5   e      55          1 
6   f      50          2
7   g      65          2
8   h      68          2   

Department table with 

dept_id  dept_name 
1        Information Technology
2        Computer Science


output should return

id  student_name  dept_name                percentage
3   c             Information Technology   90
2   b             Information Technology   80 
1   a             Information Technology   70 
4   d             Information Technology   70 
8   h             Computer Science         68  
7   g             Computer Science         65
6   f             Computer Science         50

I have tried something like

SELECT *, ROW_NUM() OVER (PARTITION BY D.Dept_ID ORDER BY S.PERCENTAGE DESC) AS ROW_NUMBER
   FROM STUDENT S, DEPARTMENT D WHERE D.DEPT_ID = S.DEPT_ID
)
SELECT ID, NAME AS STUDENT_NAME, DEPT_NAME, PERCENTAGE FROM CTE WHERE ROW_NUMBER < 4.

HERE, I am adding static condition (row_number < 4) row_number, It will give me a wrong output when there are students with the same percentage.

Please help with this.

1 Answers
Best Answer
AvatarAmit answered 2 weeks ago
Your Answer

20 + 1 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com