CODEWITHSUNDEEP
sqlsql-serverdatabase
Jaypal Sodha
Jaypal Sodha

Reputation: 2698

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

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


**expected Result**

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

you can see that, there are two student with 70% so both the student will be considered at the third place, and will be a part of top 3.

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.

Upvotes: 1

Views: 2250

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

You can try with DENSE_RANK() instead off ROW_NUMBER() as below-

SELECT A.dept_id,A.name,A.dept_name,A.percentage 
FROM
(
    SELECT D.dept_id,S.name,D.dept_name,S.percentage,
    DENSE_RANK() OVER(PARTITION BY D.dept_id ORDER BY S.[percentage] DESC) RN
    FROM Department D
    INNER JOIN Student S ON D.dept_id = S.dept_id
)A 
WHERE RN <= 3

Output is-

dept_id name    dept_name               percentage
1       c       Information Technology  90
1       b       Information Technology  80
1       d       Information Technology  70
1       a       Information Technology  70
2       h       Computer Science        68
2       g       Computer Science        65
2       f       Computer Science        50

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269733

Is this the query you are trying to write?

WITH cte as (
    SELECT s.ID, s.NAME, d.DEPT_NAME, s.PERCENTAGE,
           RANK() OVER (PARTITION BY D.Dept_ID ORDER BY S.PERCENTAGE DESC) AS seqnum
   FROM STUDENT S JOIN
        DEPARTMENT D
       ON  D.DEPT_ID = S.DEPT_ID
)
SELECT cte.ID, cte.NAME AS STUDENT_NAME, cte.DEPT_NAME, cte.PERCENTAGE
FROM CTE
WHERE seqnum < 4;

Notes:

  • Never use commas in the FROM clause.
  • Always use proper, explicit, standard JOIN syntax.
  • ROW_RANK() is not a function. I think you want RANK(), given that you want four rows when the limit is just 3.

Upvotes: 5

Related Questions