Add a rank filed to query

Question

hi

Is there anyway to add a filed that can rank a values from another filed?

thanks

Answer 1

Take a look at RowNumbering.zip in my public databases folder at:

 

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

 

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly. 

 

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

 

This little demo file includes examples of queries to compute both sequential row numbers and ranks.

---

Ken Sheridan, Stafford, England

Answer 2

Hi moaaz-96,

you can refer the example mentioned below.

I create a demo table "stud" looks like below.

I use the query mentioned below.

SELECT stud_name,marks,
       (SELECT COUNT(T1.marks) 
          FROM
                 stud AS T1 
         WHERE T1.Marks >= T2.Marks) AS Rank 
FROM 
      stud AS T2 
ORDER BY marks DESC

Output:

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

I use the query mentioned below.

SELECT stud_name,marks,
       (SELECT COUNT(T1.marks) 
          FROM
                 stud AS T1 
         WHERE T1.Marks >= T2.Marks) AS Rank 
FROM 
      stud AS T2 
ORDER BY marks DESC

Not quite right.  It should be:

 

SELECT stud_name,marks,

    (SELECT COUNT(*)+1

     FROM stud AS T1

     WHERE T1.Marks > T2.Marks) AS Rank

FROM stud AS T2

ORDER BY marks DESC;

 

As originally written your query would return incorrect rank values in the event of a tie.

---

Ken Sheridan, Stafford, England

Answer 4

Hi moaaz-96,

We find that you did not reply to any suggestion after you create this question on this forum.

you can refer the solution suggested by the community members and mark it as an answer that solve your issue.

we can see that the issue is solved.

but you did not update the status of this thread.

so please try to update the status of this thread, refer the suggestions, If you think it can solve your issue then take the mark action and help us to close this thread.

without your mark action thread will be open forever.

if you have any further question regarding this issue then let us know about that.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

i'm sorry for not reply anything i'll reply now thank you

Answer 6

hi Deepak Panchal10 , thank you

but if there is a duplicated mark in tow records the query will skip a number like this:

student1     50     1

student2     60     2

student3     60     2

student4     70     4

is it possible to not skip number 3 ?

Answer 7

Hi moaaz-96,

you can refer the example mentioned below.

I create a demo table "stud" looks like below.

I use the query mentioned below.

SELECT stud_name,marks,
       (SELECT COUNT(T1.marks) 
          FROM
                 stud AS T1 
         WHERE T1.Marks >= T2.Marks) AS Rank 
FROM 
      stud AS T2 
ORDER BY marks DESC

Output:

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

hi Deepak Panchal10 , thank you

but if there is a duplicated mark in tow records the query will skip a number like this:

student1     50     1

student2     60     2

student3     60     2

student4     70     4

is it possible to not skip number 3 ?

Answer 8

I use the query mentioned below.

SELECT stud_name,marks,
       (SELECT COUNT(T1.marks) 
          FROM
                 stud AS T1 
         WHERE T1.Marks >= T2.Marks) AS Rank 
FROM 
      stud AS T2 
ORDER BY marks DESC

Not quite right.  It should be:

 

SELECT stud_name,marks,

    (SELECT COUNT(*)+1

     FROM stud AS T1

     WHERE T1.Marks > T2.Marks) AS Rank

FROM stud AS T2

ORDER BY marks DESC;

 

As originally written your query would return incorrect rank values in the event of a tie.

---

Ken Sheridan, Stafford, England

thank you but the duplicated values make the next value rank skip a number

Answer 9

........but the duplicated values make the next value rank skip a number

That is how ranking works.  If you run a race and one other runner finishes first, two tie for second place and you finish next, then you are in fourth place not third.

If you really want what we might call 'pseudo ranking', then the following query should do that:

SELECT T1.stud_name,T1.marks,
    (SELECT COUNT(*)+1
     FROM (SELECT DISTINCT marks
                FROM stud) AS T2
                WHERE T2.marks > T1.marks) AS Rank
FROM stud AS T1
ORDER BY T1.marks DESC;

---

Ken Sheridan, Stafford, England

Answer 10

Other thread about the same subject: Rank in microsoft access without skipping any number

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 11

Ken Sheridan

so access cant display the rank like: 1,2,2,3?

Answer 12

SELECT ID, Name, mark, 
  (SELECT Count(*) 
   FROM 
    (SELECT DISTINCT t2.mark 
     FROM table1 AS t2) AS t1 
     WHERE t1.mark>=table1.mark) AS Rank 
FROM table1 
ORDER BY mark DESC

(Basically Ken's reply)

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 13

SELECT ID, Name, mark, 
  (SELECT Count(*) 
   FROM 
    (SELECT DISTINCT t2.mark 
     FROM table1 AS t2) AS t1 
     WHERE t1.mark>=table1.mark) AS Rank 
FROM table1 
ORDER BY mark DESC

(Basically Ken's reply)

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

it works perfectly now! thank you very much for the help:D

Answer 14

so access cant display the rank like: 1,2,2,3?

It can, and I gave you a query to do so. 

---

Ken Sheridan, Stafford, England