Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 <= 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<=2 AND Major1='English' AND AppliedCourse='B.A' and Plus2Percentage>=65 order by Plus2Percentage desc

SQLFiddle demo

share|improve this question

closed as unclear what you're asking by Paul White, Mark Storey-Smith, Phil, Aaron Bertrand Jun 10 '14 at 15:51

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

3  
TOP 4 without ORDER BY means "any 4". – ypercubeᵀᴹ Jun 7 '14 at 7:42
up vote 2 down vote accepted

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 <= 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 <= 30; 
share|improve this answer
    
Comments are not for extended discussion; this conversation has been moved to chat. – Paul White Oct 20 '15 at 1:54

Another way to get the TOP N per category is using the CROSS APPLY

WITH Cat AS (
  SELECT DISTINCT Category
  FROM   RKMVApplicationFormData
), CatTop AS (
  SELECT t.id, t.ApplicantName, t.Category, t.Plus2Percentage
  FROM   Cat c
         CROSS APPLY (SELECT TOP 4
                             r.id, r.ApplicantName, r.Category, r.Plus2Percentage
                      FROM   RKMVApplicationFormData r
                      WHERE  r.Category = c.Category
                      ORDER BY r.Plus2Percentage Desc) t
  WHERE  c.Category IN ('SC', 'ST', 'OBC')
)
SELECT ApplicantName, Category, Plus2Percentage
FROM   CatTop
UNION ALL
SELECT TOP 4
       ApplicantName, Category, Plus2Percentage
FROM   RKMVApplicationFormData r
WHERE  NOT EXISTS (SELECT 1
                   FROM   CatTop c
                   WHERE  c.ID = r.ID)
ORDER BY Plus2Percentage DESC

SQLFiddle demo

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.