# How to use ORDER BY with window functions
TIP
Check out the interactive database fiddle[1] to play around with the code covered in this chapter.
Let's say we want to rank the polls in order of number of votes.
So if we had 3 polls like this:
| title | vote_count |
|---|---|
| Flask vs. Django | 5 |
| Python vs. Java | 10 |
| Windows vs. Mac | 2 |
Then we want to give each a rank according to their vote count:
| title | vote_count | rank |
|---|---|---|
| Flask vs. Django | 5 | 2 |
| Python vs. Java | 10 | 1 |
| Windows vs. Mac | 2 | 3 |
We can do this using another window function called RANK(). First though, we need to get the polls and their vote count across all the options. We can do that without a window function:
SELECT polls.title, COUNT(votes) as vote_count
FROM polls
LEFT JOIN options ON options.poll_id = polls.id
LEFT JOIN votes ON votes.option_id = options.id
GROUP BY polls.title;
This gives us the first table, with title and vote count:
| title | vote_count |
|---|---|
| Flask vs. Django | 5 |
| Python vs. Java | 10 |
| Windows vs. Mac | 2 |
Now we can use the RANK() window function to go through the results and rank them in order:
SELECT
polls.title,
COUNT(votes) as vote_count,
RANK() OVER()
FROM polls
LEFT JOIN options ON options.poll_id = polls.id
LEFT JOIN votes ON votes.option_id = options.id
GROUP BY polls.title;
But we haven't told RANK() OVER() what it should rank by. Is it title? vote_count? Something else?
Since it doesn't know, every row gets the same rank (there's nothing to rank by). The result is this:
| title | vote_count | rank |
|---|---|---|
| Flask vs. Django | 5 | 1 |
| Python vs. Java | 10 | 1 |
| Windows vs. Mac | 2 | 1 |
Not exactly what we want!
Instead, we must give RANK() OVER() a column to sort by. The way to do this is by providing OVER() with a sorting column:
SELECT
polls.title,
COUNT(votes) as vote_count,
RANK() OVER(ORDER BY COUNT(votes) DESC)
FROM polls
LEFT JOIN options ON options.poll_id = polls.id
LEFT JOIN votes ON votes.option_id = options.id
GROUP BY polls.title;
Now it's more like it:
| title | vote_count | rank |
|---|---|---|
| Flask vs. Django | 5 | 2 |
| Python vs. Java | 10 | 1 |
| Windows vs. Mac | 2 | 3 |
Note that the ORDER BY inside the OVER() and the ORDER BY that we can have on the outer query can be different. This is totally valid:
SELECT
polls.title,
COUNT(votes) as vote_count,
RANK() OVER(ORDER BY COUNT(votes) DESC)
FROM polls
LEFT JOIN options ON options.poll_id = polls.id
LEFT JOIN votes ON votes.option_id = options.id
GROUP BY polls.title
ORDER BY vote_count ASC;
And the result:
| title | vote_count | rank |
|---|---|---|
| Windows vs. Mac | 2 | 3 |
| Flask vs. Django | 5 | 2 |
| Python vs. Java | 10 | 1 |