CODEWITHSUNDEEP
mysqlsqlmariadbsql-order-bywhere-clause
Thomas Jowsey
Thomas Jowsey

Reputation: 57

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.

Upvotes: 1

Views: 1765

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can do the calculation directly.

I think you want:

select 1 + count(*)
from users u
where u.power > (select u2.power from users u2 where u2.discordID = ?);

You do not actually need window functions for this purpose. With indexes on (discordId, power) and (power) this should have very very good performance.

Upvotes: 0

AmilaMGunawardana
AmilaMGunawardana

Reputation: 1830

Join the tables and then rank. select discordid, RANK() OVER
(PARTITION BY u.userid ORDER BY (the column that you are ranking from) DESC) AS Rank from otherqueryresult as q inner join user as u on u.id=q.userid

Upvotes: 1

GMB
GMB

Reputation: 222462

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

Upvotes: 4

Related Questions