question

Deepak0510 avatar image
Deepak0510 asked

Multiple select statements for a single table with union operator

SELECT ApplicantName, Category, Plus2Percentage FROM RKMVApplicationFormData WHERE (RollNo IN (SELECT TOP 4 RollNo FROM RKMVApplicationFormData WHERE Catagory = 'SC' UNION SELECT TOP 4 RollNo FROM RKMVApplicationFormData WHERE Catagory = 'ST' UNION SELECT TOP 4 RollNo FROM RKMVApplicationFormData WHERE Catagory = 'OBC')) UNION SELECT ApplicantName, Category, Plus2Percentage FROM RKMVApplicationFormData WHERE (RollNo IN (SELECT TOP 4 RollNo FROM RKMVApplicationFormData)) i'm retrieving top 4 students form each category, but in the last select statement i dont want the those student which has been already retrieved in the previous select statements. Through last select statement i want top 4 students from all the category(SC,ST,OBC) but excluding the previous top 4's
selectuniontopselectivity
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What criteria determines the 'top' students - there's no order by in any of those queries - as it stands it is just 4 random rows
1 Like 1 ·

1 Answer

·
Jeff O avatar image
Jeff O answered
If you use a common table expression (WITH), you can wrap the whole top 4 union selects into one place and then include them (IN) and in your second part exclude them (NOT IN) ;with topFour as ( SELECT TOP 4 RollNo FROM RKMVApplicationFormData WHERE Catagory = 'SC' UNION SELECT TOP 4 RollNo FROM RKMVApplicationFormData WHERE Catagory = 'ST' UNION SELECT TOP 4 RollNo FROM RKMVApplicationFormData WHERE Catagory = 'OBC' ) SELECT ApplicantName, Category, Plus2Percentage FROM RKMVApplicationFormData WHERE (RollNo IN (Select RollNo from topFour)) UNION SELECT ApplicantName, Category, Plus2Percentage FROM RKMVApplicationFormData WHERE (RollNo IN (SELECT TOP 4 RollNo FROM RKMVApplicationFormData WHERE RollNo Not In (select Roll No from topFour)))
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.