How to do arrange data of 2 tables in descending order and then intersect two tables?

Question

Question: Check which subject is among the top 3 scoring subjects for Rahul in Term-1 and Term-2 using mssql?

If I use order by before intersect the code is not working. But if I don't sort data of both the tables in descending order individually then top 3 selection would be wrong.

Please help

My approach: select top 3 subject from RESULT where exam=‘Term_1’ Order by marks desc intersect select top 3 subject from RESULT where exam=‘Term_2’ Order by marks desc

Answer 1

As you want an intersection, you can write two ranked queries to get your top 3 in each exam and then intersect the results.

With term_1_top3 as
(SELECT 
    subject
    ,RANK() OVER(PARTITION BY exam ORDER BY marks DESC) as rn1
 FROM Result
 WHERE exam='Term_1'
),
term_2_top3 as
(SELECT 
    subject
    ,RANK() OVER(PARTITION BY exam ORDER BY marks DESC) as rn2
 FROM Result
 WHERE exam='Term_2'
)

SELECT subject FROM term_1_top3 WHERE rn1<4
INTERSECT    
SELECT subject FROM term_2_top3 WHERE rn2<4;

Hope this helps. Crating two ranked queries instead of ordering them is the difference in what you have tried.

Answer 2

We can use the RANK() analytic function here:

WITH cte AS (
    SELECT subject, RANK() OVER (PARTITION BY exam ORDER BY marks DESC) rnk
    FROM RESULT
    WHERE exam IN ('Term_1', 'Term_2')
)

SELECT DISTINCT subject
FROM cte
WHERE rnk <= 3;