Getting Ranked Position - SQL - Simple Query

Question

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.

Answer 1

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

Answer 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