sql – Is there a way to filter through the results of a SELECT query, with another SELECT query?

Specifically, what I am trying to do is rank a table by a column value, referred here as power, and then take that ranked table and find a specific row, or a user in this case. Much like what would be done in a video game leaderboard to find the ranking of a specific user.

Basically, I would like to find a specific row in a table that has been ordered. I know how to do both, but not together.

I guess what I’m trying to do is combine these statements:

Sorting:

SELECT * FROM users ORDER BY power DESC;

Filtering:

SELECT * FROM otherQueryResult WHERE discordID = discordIDInput;

And then find out the row number of the row that is returned.

Realistically, I’d like to have a result that looks something like this:

+-----------+------+
| discordID | rank |
+-----------+------+
| 123456789 |  52  |
+-----------+------+

Being 14, my SQL knowledge is still very limited – so I would prefer simpler solutions over raw performance or usability at the moment, however any help is appreciated.

Thanks in advance for any help.

Answer

You seem to be looking for a window function such as RANK():

SELECT * 
FROM (SELECT u.*, RANK() OVER(ORDER BY power DESC) rnk FROM users u) x 
WHERE discordID = ?

The inner query assigns a rank to each user, with the user having the highest power ranked first. Then, the outer query filters the user that has the relevant discordID.

There are other window functions that can respond to your use case:

• ROW_NUMBER(): assigns a rank to each record ; even-records are not treated consistently

• RANK(): even-records get the same rank ; if two users have rank 1, then the following user has rank 3

• DENSE_RANK(): same as RANK(), but does not create gaps in the ranks