Instructor: Hi guys, and welcome back.
In this video, I wanted to tell you about
the DISTINCT keyword in Postgres and how we can use it.
As an example, for this lecture,
we're gonna try to get the most voted option for each poll.
And we're gonna show you the example data first.
So here we've got what our data is gonna look like.
We've got some polls,
two of them at the moment,
Flask versus Django,
PostgreSQL versus MySQL,
we've got five options three for poll One,
and two for poll two.
And we have ourselves some votes.
So here's three different users
voting on these options differently.
Now, as well as this,
we're gonna have some sections down here for the query
that we're gonna run on the left.
And the output that we're gonna get
if we run that query on the right.
We're gonna start with a query
that we already know how to build,
and but it's a bit long,
so I'll quickly go through it.
We're selecting the options table columns,
and the vote count from the votes table
and from the options table, joining on votes.
And grouping by option ID.
So that's gonna give us something like this,
do take a moment if you need to pause it
and understand sort of how we're arriving at this data.
But this is essentially what we're gonna end up getting
for each option,
how many votes were cast, essentially.
This doesn't quite give us what we wanted.
So the next thing is we have to do some sorting.
And we're gonna start off by sorting by vote count.
So I'm adding an order by vote count
in descending order there.
And what that's gonna do is it's going to resort our data,
give us the options with the highest vote count at the top.
And this is gonna be a bit closer to what we want.
But there is a small problem,
which is if we have two options for the same poll
and both of those options have, let's say 10 votes,
they are gonna to the top
even though they're in the same poll.
Because the next most voted option would be down here
and that would only have two votes.
So we could potentially end up with many options
with poll ID one at the top,
and then some other options with a different poll ID
at the bottom.
So if we want to get the top option for each poll,
then this query here
isn't quite gonna give us what we want.
So in order to help us achieve what we want,
I'm gonna add another ORDER BY
which is ORDER BY poll ID first.
That way, what we're getting now
is for each poll,
the options sorted by vote count.
Now this is a bit more useful
because now what we can do is we can go and grab
the first or the top item of each poll ID,
we can say, Okay,
now what I wanna do is I wanna grab this data set,
and from each poll ID
grab the top element only.
And we can do that because we have sorted by poll ID.
And the way we do that,
is with the DISTINCT ON clause,
for DISTINCT ON essentially groups by this column.
Options dot poll ID,
but only grabs the first row of each group.
And so that's essentially what we're doing
what we wanted to do with the previous table,
we can do with DISTINCT ON.
Something important is that
you have to ORDER BY poll ID
as the first thing your ordering by in this clause,
if you want to use DISTINCT ON.
So clearly, I've been planning this set of queries
in order to lead us here.
And but this is what you have to do
in order to use DISTINCT ON.
And what DISTINCT ON does is it grabs the first row
of each group
that would be determined by grouping on options dot poll ID.
I know this is quite complicated,
but do and pause the video, give it a try.
Run the queries yourself.
Look at the official documentation.
You know, different explanations
are always gonna help as well.
And in the end, what we do end up with is the two options,
one for each poll ID
and they are the highest vote count options in each poll.
There you go, the ORDER BY poll ID is necessary.
At he top of the ORDER BY clause.
So the DISTINCT ON clause or command
allows us to keep the first row of the returned results
after grouping and order.
It's unique to PostgreSQL by the way,
so don't go try this on MySQL.
Clearly, when we vote on Postgres versus MySQL,
Postgres must always come ahead.
And this is unique to Postgres.
DISTINCT without the ON is a different thing entirely
DISTINCT is used immediately after the select statement,
and it applies to all the columns been selected,
and the database will only return those rows
for which columns set has unique values.
This is much simpler, so I'm gonna through this now,
let's say that we've got some votes here,
the same as before,
and we do select DISTINCT username from votes,
that's just gonna give us one unique usernames really.
I said that you apply the DISTINCT keyword after select
and it applies to all columns being selected.
So let's see that we've got some cities like this,
and we've got essentially four cities
but the have the same name,
two of them have the same names.
And so you're gonna just select DISTINCT name
from cities, that's going to give you the unique names,
or rather, is gonna give you the unique values
for the name column that you've selected.
But if we do something like select distinct name
comma my county.
The DISTINCT clause applies to both name and county.
So now what you're gonna get
is Springfield, Essex one time,
because Springfield, Essex is unique in its own group,
and then Washington, Massachusetts and Washington, London.
And so DISTINCT here
is getting you that unique combination of name and county,
so Springfield, Essex is a unique combination,
Springfield, Essex here is not unique
because it was already there at the top.
But Washington, Massachusetts is unique as a combination.
And so is Washington, London.
I made up the cities by the way, so yeah.
So a quick recap then
DISTINCT ON applies on a single column
or multiple columns,
but you can pick which columns you want to apply it on.
Essentially what it does is it mimics a GROUP BY
and gives you just the first row of the group.
DISTINCT though applies on all columns being selected
and you can't pick which ones it does apply on.
Finally DISTINCT ON requires an order by for consistency.
If you don't have the ORDER BY
then getting the first column
of the group wouldn't really make sense
because the order would essentially be random
or not guaranteed.
All right, hope this makes a little bit of sense.
Thank you for joining me in this video.
Thanks for watching, and I'll see you in the next one.