0

I have a very simple database structure and am struggling to write a command to find the ranked position of the user.

------------------
id     |  average
------------------
1      |  79
------------------
2      |  99
------------------
3      |  22

How would I be able to find the ranked position of the user with id 1? #2 would be ranked 1st. I want the rank to be descending with the highest number in 1st place.

2
  • 1
    Please, tag your MySQL version. Latest versions allow to use window functions like RANK()
    – McNets
    Jun 25 '19 at 15:15
  • If you could provide more information about the data you are looking at and what you have tried so far it would help
    – Joe W
    Jun 25 '19 at 15:56
0

If you are in version 8 (or above), you can use the RANK() function for this purpose:

SELECT
    id,
    average,
    RANK() OVER (ORDER BY average DESC) as rk
FROM
    t;

Note I've ordered the result by average DESC, that assigns rank=1 to the maximum average.

id | average | rk
-: | ------: | -:
 2 |      99 |  1
 1 |      79 |  2
 3 |      22 |  3

db<>fiddle here

-2

assuming average is the column name -

add order by Average Desc on the end of your query

have a look at the answers to this question for further information

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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