Return Rank In SQL Query Based On Multiple Columns

Question

I have a large SQL table called 'allscores' similar to the following:

user    score    quiz_id   high_score
Bob     90       math      1
John    80       math      0
John    85       math      1
Steve   100      math      1
Bob     95       reading   0
Bob     100      reading   1
John    80       reading   1

The 'high_score' field is in the table to begin with and is always set to '1' for the row where a user's score is the highest for them for that quiz.

What I want is a SQL query that I can run on an individual user to pull their highest score from each of the two quizzes ('math' and 'reading') along with their overall rank among scores for that quiz. What I have so far is the following:

SELECT `user`, `score`, `quiz_id` FROM `allscores` WHERE `user`="Bob" AND `high_score`="1"

Which will output the following:

user    score    quiz_id
Bob     90       math
Bob     100      reading

This query is simply pulling the highest score for Bob from each quiz - what I want to add is the ranking of the score among the scores in that particular quiz - so an output like the following:

user    score    quiz_id   rank
Bob     90       math      2
Bob     100      reading   1

Bob's rank is '2' for the math quiz as Steve has a higher score, but he is ranked '1' for reading as he has the highest score.

How would I add this ranking column to my existing query?

And when ranking, how do you handle ties? – Shawn Jan 31 '18 at 20:09 — Shawn, Jan 31 '18 at 20:09

Shawn · Answer 1 · 2018-01-31T20:34:10.653

This uses MS T-SQL syntax, but if your flavor of SQL uses window functions, it should be similar.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE t ( 
    [user] varchar(10)
  , score int
  , quiz_id varchar(10)
  , high_score bit
) ;

INSERT INTO t ([user], score, quiz_id, high_score)
VALUES
  ( 'Bob',90,'math',1 ) 
, ( 'John',80,'math',0 )
, ( 'Steve',100,'math',1 )
, ( 'Bob',95,'reading',0 )
, ( 'Bob',100,'reading',1 )
, ( 'John',85,'math',1 )
, ( 'John',80,'reading',1 )
;

MAIN QUERY:

SELECT s1.[user]
  , s1.score
  , s1.quiz_id
  , s1.high_score
  --, s1.isUserHighScore
  , s1.ranking
FROM (

  SELECT 
      t.[user]
    , t.score
    , t.quiz_id
    , t.high_score
    --, ROW_NUMBER() OVER (PARTITION BY t.[user],t.quiz_id ORDER BY t.score DESC) AS isUserHighScore
    , DENSE_RANK() OVER (PARTITION BY t.quiz_id ORDER BY t.score DESC ) AS ranking
  FROM t

) s1

WHERE s1.[user]='Bob'
  --AND s1.isUserHighScore = 1
    AND s1.high_score = 1

Results:

| user | score | quiz_id | high_score | isUserHighScore | ranking |
|------|-------|---------|------------|-----------------|---------|
|  Bob |    90 |    math |       true |               1 |       2 |
|  Bob |   100 | reading |       true |               1 |       1 |

I use ROW_NUMBER() to determine the highest score in a quiz for a user, then use DENSE_RANK() to figure out the rank of a users score versus others. The difference between DENSE_RANK() and RANK() is essentially that DENSE_RANK() won't leave any gaps in the ranking. Example: 2 people scored a 90 and 1 scored an 80, then with DENSE_RANK() the 90s would both be Rank 1 and the 80 would be Rank 2. With RANK(), the 90s would be Rank 1 and the 80 would be Rank 3.

Sorry, just realized my `isUserHighScore` and your `high_score` were essentially the same thing. I misread it the first time and thought your `high_score` referred to the highest score of the quiz. — Shawn, Jan 31 '18 at 20:32
http://sqlfiddle.com/#!18/8a62c/1 <<< Illustrates difference in what `DENSE_RANK()` and `RANK()` return. — Shawn, Jan 31 '18 at 20:42
actually I need MySQL 5.6 code for this - I tried this but can't use in phpmyadmin as the RANK() function is not part of MySQL 5.6 — Darin Hawley, Jan 31 '18 at 22:20
would like to handle ties with the same methodology of RANK() - i.e. they get the same rank number. — Darin Hawley, Jan 31 '18 at 22:27
Check this one out. https://stackoverflow.com/questions/45204889/rank-over-partition-by-in-mysql — Shawn, Feb 01 '18 at 14:19

Return Rank In SQL Query Based On Multiple Columns

1 Answers1