CODEWITHSUNDEEP
mysqlsql
Ryan
Ryan

Reputation: 155

Select in Select Rank()

Can someone point me in the direction of how I can select a particular username and their rank from the resulting query? I'm trying to retrieve FoxDown's rank.

I believe I need to select from this query somehow Select username, my_rank (Select.....

Current Query

    SELECT
    username,
    SUM(goals) as Diff,
    RANK() OVER (ORDER BY Diff DESC) my_rank
    FROM results
    WHERE Week = 'Week 23'
    GROUP by username
    ORDER by my_rank

Query resulting table 

username  Diff  my_rank
Sugar      12     1
Coj        10     2
Gunners     8     3
Captain     6     4
Spoon       6     4
Ric         6     4
FoxDown     6     4
shau        4     8
Josh        4     8
nol         4     8
pesty       4     8
Hill        4     8
Jg          4     8

Upvotes: 1

Views: 84

Answers (2)

Ryan
Ryan

Reputation: 155

This worked!! 

WITH players_results AS(
    SELECT
        username,
        SUM(goals) as Diff,
        RANK() OVER (    
            ORDER BY Diff DESC      
        ) my_rank
    FROM
        players_results
        WHERE matchweek = 'Matchweek 24'
        GROUP by username
        ORDER by my_rank
    )
    SELECT 
        * 
    FROM 
        players_results
    WHERE 
        username = 'FoxDown'

Upvotes: 1

Lukas Eder
Lukas Eder

Reputation: 220842

Window functions are calculated after the where clause, logically. So you have to nest your window function calculation in a derived table, and then filter on that:

SELECT *
FROM (
  SELECT
    username,
    SUM(goals) as Diff,
    RANK() OVER (ORDER BY SUM(goals) DESC) my_rank
  FROM results
  WHERE Week = 'Week 23'
  GROUP by username
) t
WHERE username = 'FoxDown'

Note that you cannot reference a column alias from the same SELECT level, so you need to repeat the SUM(goals) aggregate function in the rank calculation. This works again because aggregate functions are calculated before window functions, logically (see the same linked blog post)

Upvotes: 1

Related Questions