In this video, we're going to talk about the distinct clause which we can use to eliminate duplicate
results, only getting distinct results in a query.
So it looks like this We might want to select the distinct author last names from books.
The most important thing to remember is that distinct goes after select before a column name.
So if we come back here, let's do a select just author, last name from books.
There's quite a few of them in here, but there's also quite a few duplicates.
Neil Gaiman.
Well, Gaiman is in here twice.
Eggers is in here twice.
Gaiman again, actually.
So three times.
Carver is in there twice.
Foster Wallace is in there twice.
And we have two Harrises.
But they're actually not the same first name.
They're not the same author, but we don't care about that or the query doesn't care about that.
We're just getting all author last names.
So if we do a distinct clause before author name, same exact query, otherwise select distinct author
name.
From books.
We had 19 rows here in the first query of all last names.
Here we only have 11.
So we have the here we once.
GAIMAN Once.
EGGERS Once.
I think the hero is actually only once in the original, but Gaiman was three times.
Now we only have one.
GAIMAN One Harris, one Foster Wallace, one Carver.
Okay.
So that's one example of using distinct let's try another let's do a select released year from books.
This is every book's year it was released and I'm sure there's duplicates.
2003 2003.
Is there another example?
I'm sure there is 2001, 2001.
So we have 19 rose once again, we're getting every row in the data set.
Now, if I instead select distinct released year from books, well, we get 16 only distinct results.
So next up, I have a question, which is what about getting distinct author full names?
Because at the moment we're only doing last names.
If I recall this, let's actually do select start from books or maybe select author F name comma, author,
L name from books.
And we have at least one duplicated last name where it's not the same person.
Dan Harris.
Frieda Harris Well, they're being condensed together.
They're treated the same.
When we simply get unique or distinct last names, we lose Frieda Harris.
So how do I get the distinct combination of the two?
There's two solutions, at least that I can think of.
One of which you should already know.
Well, you might be able to think of.
It involves a string function.
We've already learned it's a little longer, but that option would be to use Kan cat.
So instead of finding the distinct last names, we will concatenate first name and last name, maybe
with a space and find the distinct results of that.
So it looks something like that.
Or like this select and then can cat author f name and then maybe a space and then author l name.
From books.
And then I want to find the distinct ones of these.
So Neil Gaiman and Neil Gaiman, Well, those are not unique.
So we only get one of them.
But then Dan Harris and Frieda Harris, they are now unique, so they're considered distinct.
Both of them would stay in our dataset.
So let's do select distinct can cat author F name author ll name distinct.
And there we are.
We only get one Neil Gaiman or Dave Eggers or Raymond Carver, but we still have Dan Harris and Frieda
Harris.
So there's 12 distinct authors, at least based off of first name and last name with a space between
them.
I guess, you know, hypothetically, we could have authors who have the same name.
I'm sure there are, you know, like an Amazon data set.
There's thousands of authors, I'm sure, that have shared names.
So they're all going to have IDs and something assigned to them.
But for this, it's working.
But there's a second way that's easier and cleaner to select distinct full names, which is just to
use select distinct author F name.
Comma.
Author l name from books.
And what we're saying here is not select the distinct, distinct first names and then also later the
last names.
Instead, it's saying select the distinct first name and last name combinations.
And there we are.
Jhumpa Lahiri.
Neil Gaiman.
Dave Eggers.
We get 12 rows, the same results we had earlier.
So if I were to change this and add a third column in, you'll see how it works.
Let's do released here.
Now we're saying find the distinct combinations of author, full name, first name, author, last name
and released year.
Well, unless we have Dan Harris and Frieda Harris with the same released year for their books.
Which we don't write when it's 2014, when it's 2001.
We basically just get every row in our data set because all of these combinations are distinct.
We have Neil Gaiman, 2016, Neil Gaiman, two, one.
All of these would have to be the same.
All three columns would have to be the same and duplicated in order for distinct to do anything.
But that's not what we want.
The first example that we cared about was just the distinct combo of first name and last name.
So that's the simpler way.
You don't have to use Kit-Kat.
You can just do this, separate them by commas.
You don't even need parentheses distinct.
We'll just figure out that you're working with this combination of columns if you separate them by commas.
So that's distinct.
We use that a lot.
Make sure it comes after select before one or more column names.