sqlsql-order-byintersectjointjsconsolidation

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


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


Solution

  • 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.