multiple select query for the same tables with union Using below query i want to find out the top number of students from each catagory(SC,ST,OBC,etc) on plus2percentage basis. eg i want top 3 students from SC catagory, top 2 from ST and Top3 from OBC. But i also want the top 5 from the open catagory, which include all catgory(SC,ST,OBC). but excluding the previous top 8 students from SC,ST,OBC Catagory.

So the final out output i want is like

top 3 students from SC Catagory. top 2 students from ST Catagory. top 3 students from OBC Catagory. top 5 student from(SC/ST/OBC/OPEN)but no one from the previous 8 students.

Total number of top Student= 13



WITH PRIMARY_CHOICE AS ( SELECT RollNo, ApplicantName, Catagory, Plus2Percentage,AppliedCourse, Major1 FROM (
            SELECT RollNo, ApplicantName, Catagory, Plus2Percentage, AppliedCourse,Major1,  row_number() over (partition by Catagory order by Plus2Percentage desc) as rn
            FROM RKMVApplicationFormData) as T 
            WHERE rn &lt;= CASE WHEN Catagory='SC'  AND Major1='English' AND AppliedCourse='B.A'  AND Plus2Percentage >= 50  THEN 2
                           WHEN Catagory='ST'  AND Major1='English' AND AppliedCourse='B.A'  AND Plus2Percentage >= 60 THEN 2
                           WHEN Catagory='OBC'  AND Major1='English' AND AppliedCourse='B.A' AND Plus2Percentage >=50 THEn 1
                            ELSE 0
END ) 
SELECT RollNo, ApplicantName,  Catagory, Plus2Percentage,AppliedCourse,Major1  FROM PRIMARY_CHOICE UNION ALL
SELECT RollNo, ApplicantName,  Catagory, Plus2Percentage,AppliedCourse,Major1
FROM ( SELECT RollNo, ApplicantName, Catagory, Plus2Percentage,AppliedCourse,Major1,  row_number() over (order by Plus2Percentage desc) as rn
FROM RKMVApplicationFormData x 
WHERE NOT EXISTS ( 
select 1 from primary_choice y 
where x.RollNo = y.RollNo ) ) AS T2 
WHERE rn&lt;=2 AND Major1='English' AND AppliedCourse='B.A' and Plus2Percentage>=65  order by Plus2Percentage desc

SQLFiddle demo
I'm not entirely clear on what you are trying to do, how should the top 4 in each category be chosen for example, randomly?

WITH PRIMARY_CHOICE AS (
  SELECT ApplicantName, Category, Plus2Percentage
  FROM (
    SELECT ApplicantName, Category, Plus2Percentage
         , row_number() over (partition by Category
                              order by Plus2Percentage desc) as rn
    FROM RKMVApplicationFormData
  ) as T 
  WHERE rn &lt;= CASE CATEGORY WHEN 'SC' THEN 25
                            WHEN 'ST' THEN 30
                            WHEN 'OBC' THEn 15
                            ELSE 0
              END
)
SELECT ApplicantName, Category, Plus2Percentage
FROM PRIMARY_CHOICE
UNION ALL
SELECT ApplicantName, Category, Plus2Percentage
FROM (
    SELECT ApplicantName, Category, Plus2Percentage
         , row_number() over (order by Plus2Percentage desc) as rn
    FROM RKMVApplicationFormData x
    WHERE NOT EXISTS (
        select 1 from primary_choice y
        where x.ApplicantName = y.ApplicantName
    )
) AS T2
WHERE rn &lt;= 30;