Take the 2-minute tour ×
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.

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

2  
TOP 4 without ORDER BY means "any 4". –  ypercube Jun 7 at 7:42
    
Order By Plus2Percentage Desc –  Deepak Jun 7 at 7:46

2 Answers 2

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
    
eg. total seats in college = 100. Seats Reserved for category SC = 25 , ST = 30, OBC = 15 and 30 seats are open. First i have to find out top 25,30,15 students from the resevd category on the basis of their percentage in 10+2. after that i hv to find out top 30 student form ecah category sc/st/obc and open on the basis of 10+2 percentage, but excluding the previous 70 students selceted from the reserved category. –  Deepak Jun 7 at 7:05
1  
But what does top refer to? Why should I choose Applicant A over Applicant B in category 'SC'? –  Lennart Jun 7 at 7:29
    
top refers to the highest number of percentage in 10+2(Plus2Percentage) –  Deepak Jun 7 at 7:49
    
I dont know what 10+2 is, but you can add an order by clause, I'll update the example –  Lennart Jun 7 at 7:51
    
ok lemmme try again...eg. there are 100 seats, i want to find out the top 25 from SC category, top 30 from ST and so on.... and 10+2 percaentge means a over all percentage in 12th standard or 12th class in school –  Deepak Jun 7 at 7:55

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.