Hi guys and welcome back!
In this video we're going to learn
how we can use the Order by Clause
with a window function and why that can be useful.
Let's get started.
Here we've got the sample data
for this project and what we have is essentially
the polls that we have in our poll application.
So, we've got the polls table with an ID title
and the username, the options table
with the ID, the option text and the poll ID.
And we've got the votes table with the username,
the option ID and of course that foreign key thing.
Which is by the way not necessary
I just included it here for the sake of example.
In the polls table we've got three polls,
the Flask versus Django, Python versus Java,
Windows versus Mac and then we've got a few options
for each and we've got a bunch of votes for each.
We're not really worried about who voted
for what or anything like that.
What we want to do is we want to grab all the polls
and we then want to rank them in order
of how many votes they each got.
So, the one with the most votes
is gonna get the number one attached to it
and the one with the least votes
is gonna get the number three attached to it.
We're gonna do that using window functions
and first of all we have to go ahead
and grab the poll titles as well as the vote counts.
And we can do that without a window function.
So, let me just type that out real quick.
So, here we have the polls title being selected
the count of votes as vote count
and we're selecting from polls, left join on options,
left join on votes and then we're grouping by,
this should be polls.titles since
it's what we're selecting up here.
So, let me just run this and you'll see
that all these rows get created, that's fine.
And then we have our different polls in here.
What we got is each poll and how many votes they have.
So, we can verify this just by looking into the votes
but of course, we are comfortable enough at this point
to realise that this is probably gonna be correct.
So, now that we've got this
We do have to think about how we're going to select
one more column so it's probably gonna be here
in the select part of the query.
We want to add one more column and we can use
the rank function to do that.
However, remember that when we run something here
that only has access to the current row.
So, therefore if we have access to the current row
we cannot possibly rank it against other rows,
we just don't have that data.
That's why the rank function requires a window
so, we do rank over.
If we run this now, you're going to see
that we end up with the rank.
So, we've got rank one, rank one, rank one.
Obviously, not what we wanted but nonetheless
we've added a number there.
The reason why we're getting rank one for everything
is because we haven't told the rank over
what it should rank by.
Is it the title, is it the vote count,
is it something else potentially.
And since it doesn't know, then every row
gets the same rank because there's nothing to rank by.
So, we need to give it a column to sort by.
And the way we do that is we do order by count
of votes and we're gonna do descending order.
What this tells the rank function is that the thing
with the most values in this count of votes
is gonna get the first number.
And then it's gonna move over
to the next number and so on.
So, now if we run this, you'll see that we get
ranks one, two, and three.
Note that the order by here in the window function
is completely separate from what we can include down here.
So, we could order by vote counting ascending order
for example and that would sort by this column,
the final results but this here only effects the order
in which the window is evaluated for ranking purposes.
If we run this, you'll see that we get the vote
count of two first with a rank of three.
So, these are separated and evaluated individually.
Thank you guys for watching this video.
I hope you've learned something
and I'll see you in the next one.