0

Here's my problem:

  1. If a student gets a score of 49 and below in any subject, he won't be included in the ranking.

  2. Those students who will get above 49 in all subjects shall be rank according to their average

  3. And if the students get an equal average, their ranks shall be equal.

Here is my sample:

Table1                      
Student_ID  Name    Math    English   Science   Average 
   1        Apple    64       49        70      61.00   
   2        Boy      80       79        65      74.67   
   3        Cat      51       78        66      65.00   
   4        Dove     50       76        64      63.33   
   5        Eden     81       88        72      80.33   
   6        Fox      80       79        65      74.67   
   7        Golf     32       88        69      63.00   


Output                      
Student_ID  Name    Math    English   Science   Average    RANK
   1        Apple    64       49        70      61.00   
   2        Boy      80       79        65      74.67        2
   3        Cat      51       78        66      65.00        3
   4        Dove     50       76        64      63.33        4  
   5        Eden     81       88        72      80.33        1
   6        Fox      80       79        65      74.67        2
   7        Golf     32       88        69      63.00

And here is my query:

SELECT
    tmain.Student_ID,
    tmain.Name,
    tmain.Math,
    tmain.English,
    tmain.Science,
    tmain.Average,
    IIf(sub.RANK=0, Null, sub.RANK) AS RANK
FROM
    Table1 AS tmain
    LEFT JOIN
    (
        SELECT
            t1.Student_ID,
            t1.Average,
            (
                SELECT Count(*)
                FROM Table1 AS t2
                WHERE
                        t2.Math>49
                    AND t2.English>49
                    AND t2.Science>49
                    AND t2.Average>=t1.Average                    
            ) AS RANK
        FROM Table1 AS t1
        WHERE
                t1.Math>49
            AND t1.English>49
            AND t1.Science>49
    ) AS sub
    ON tmain.Student_ID = sub.Student_ID;

Output based on query:

Output                      
Student_ID  Name    Math    English   Science   Average    RANK
   1        Apple    64       49        70      61.00   
   2        Boy      80       79        65      74.67        3
   3        Cat      51       78        66      65.00        4
   4        Dove     50       76        64      63.33        5  
   5        Eden     81       88        72      80.33        1
   6        Fox      80       79        65      74.67        3
   7        Golf     32       88        69      63.00

Can someone help me how to fix this, the output skipped the 2nd rank

Note: Rank values are not stored in the table.

nine05
  • 11
  • 2
  • 3
    You should make an attempt at solving the problems you post here. Can you write a query to return any data at all? – dcaswell Aug 26 '13 at 20:41
  • Sir @user814064 sorry for not including my query in my question. I edited my post and include my query and its output. – nine05 Aug 27 '13 at 07:29
  • Both of your #3's have identical grades for every class. How do you want to solve for that? How do you want to determine who is #2 and who is #3? – Johnny Bones Aug 29 '13 at 14:23
  • I want Student_ID 2 and 6 to be 2nd not 3rd. The output skipped the 2nd rank – nine05 Aug 29 '13 at 20:16

1 Answers1

0

Unless I'm misunderstanding you, this will give you exactly what you need:

SELECT tblStudents.StudentID, 
  tblStudents.SName, 
  tblStudents.Math, 
  tblStudents.English, 
  tblStudents.Science, 
  tblStudents.AvgScore, 
  tblStudents.Rank
FROM tblStudents
WHERE 
  (((tblStudents.Math)>49) AND 
   ((tblStudents.English)>49) AND 
   ((tblStudents.Science)>49))
ORDER BY tblStudents.AvgScore DESC;
Johnny Bones
  • 8,786
  • 7
  • 52
  • 117