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