How to use PARTITION with window functions

Another example with polls^[1]. This time, we want to rank each poll's options by their vote count.

Here's the sample data for polls, options, and votes:

title	owner_username
Flask vs. Django	jose
Python vs. Java	rolf

id	option_text	poll_id
1	Flask	1
2	Django	1
3	It depends	1
4	Python	2
5	Java	2

username	option_id
jose	1
jose	4
rolf	1
annne	1
bob	4
rolf	5
charlie	2

If we get options and vote count, it would look like this:

option_text	vote_count
Flask	3
Django	1
It depends	0
Python	2
Java	1

And what we want to end up with is this:

title	option_text	vote_count	rank
Flask vs. Django	Flask	3	1
Flask vs. Django	Django	1	2
Flask vs. Django	It depends	0	3
Python vs. Java	Python	2	1
Python vs. Java	Java	1	2

As you can see, within each poll, the options are ranked by vote count. That means that there can be multiple options with the same rank, but not under the same poll.

Let's talk about how you can do this.

First, let's get poll title, option texts, and vote counts:

SELECT
  polls.title,
  options.option_text,
  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.id, options.id;

We get this:

title	option_text	vote_count
Flask vs. Django	Flask	3
Flask vs. Django	Django	1
Flask vs. Django	It depends	0
Python vs. Java	Python	2
Python vs. Java	Java	1

We can now use the RANK() function, but what should we ORDER BY?

If we do ORDER BY COUNT(votes) DESC we won't get exactly what we want. Let me show you:

SELECT
  polls.title,
  options.option_text,
  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.id, options.id;

title	option_text	vote_count	rank
Flask vs. Django	Flask	3	1
Flask vs. Django	Django	1	3
Flask vs. Django	It depends	0	5
Python vs. Java	Python	2	2
Python vs. Java	Java	1	3

Since we just ordered, we've told RANK what column to use for ranking, but we haven't told it to treat each poll as a separate group of ranks.

TIP

Something else to note here is that Django and Java both have 1 vote, and they have the same rank of 3.

Then, there's no rank 4! It was skipped since two rows have a rank of 3.

If you wanted to change rank 5 to rank 4, you can use DENSE_RANK() instead.

Let's split the groups so that the window function is applied on each poll separately:

SELECT
  polls.title,
  options.option_text,
  COUNT(votes) AS vote_count,
  RANK() OVER(PARTITION BY polls.id ORDER BY COUNT(votes) DESC)
FROM polls

And now, finally we get what we were after!

title	option_text	vote_count	rank
Flask vs. Django	Flask	3	1
Flask vs. Django	Django	1	2
Flask vs. Django	It depends	0	3
Python vs. Java	Python	2	1
Python vs. Java	Java	1	2

---

1. Interactive database fiddle for this chapter (opens new window) ↩︎