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
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
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)))
15 People are following this question.
