Now then I don't really like now, then I'll use.
All right.
Too much.
Okay.
How about up next, we're going to talk about distinct.
So distinct is something that we use in conjunction with Select.
So when we're selecting data out, let's say we're doing something like give me all book titles from
the database and we may have duplicates in there.
And we would get all those duplicates because we just asked for all titles.
But if I say give me all distinct titles, it will do what that sounds like.
It will only give us the distinct or unique titles.
So without further ado, let's take a look at it in use.
So here's an example.
The first thing that you should notice is that distinct comes right after Select.
So we're saying select author last name.
So there's a bunch of authors and in our table some books have or some authors have multiple books.
So David Foster Wallace, for example, has two books in there.
So when we say select author last name, we get two results.
We get David Foster Wallace.
And then again, David, if we're only doing last name, we get Foster Wallace and Foster Wallace.
So if I wanted to know only the unique or the distinct author last names in my books table, I would
write this query here select.
And the only thing that's different is the distinct so select distinct author name from books.
So let's try it out.
We'll go over here, make some space, and let's just start just to show you what I'm talking about
by select author without the distinct from books just like that.
And as you can see, we have gay men who's in here twice.
Eggers is in there twice.
Carver is in there twice.
Foster Wallace is in there twice.
And Harris is in there twice.
However, there's something kind of weird about Harris that we'll take a look at in just a moment.
So if we only want to just get a survey of all the authors in our database, but we don't want duplicates.
It's as simple as select distinct author l name from books just like that.
Hit Enter.
Now it's much shorter because those duplicates are gone.
So now we only have one game and one Eggers, one Foster Wallace and one Harris.
And of course, we could do that for other things.
So let's try it.
Four years.
So we have select released year from books.
You can see.
Let's see.
There are some books that have duplicates.
So, for example, 2003.
There's two 2003 se at least I think there's multiple there's at least one more.
When I designed this or when I created the data, let's just focus on 2003.
So there's two of them there.
So if we do a select distinct.
Released year from books.
Our list is shorter and now we only have one 2003.
So the reason I show it to you is not just to show you another way or another example, but also we're
working with numbers here and that's important to remember that released year is an INT, it's not a
bar chart, but distinct still works.
There's one more advanced thing that I want to talk about here, which has to do with when we did our
select.
Let's start again with author LL Name from books when we did this remember that there's and actually
let's do author f name and l name so.
When we did this, you know, remember, we have Neil Gaiman twice and Dave Eggers and so on.
But then there was Dan Harris and Frieda Harris, two different people, two different authors with
the same last name, which is a pretty common situation.
So when I do distinct select distinct author l name from books.
It reduces them to just the distinct last name, which is what you would expect.
It only gives us Harris once, which makes sense.
We're asking four distinct author last names.
But what about distinct full names?
What if I want every distinct author's full name printed out?
So basically, what if I just want to know how many distinct authors there are not just based off of
last names or just first names, but both need to be distinct.
So that Dan Harris is distinct from Frieda Harris.
There are a couple of ways of doing it.
One, you already know at this point, you can use Ken Cat.
So I'll show you that.
Now, what we could do is can Kat author f name and author ll name and then ask or select distinct versions
of those.
So if I make a new file, this one will be won't be too long.
But I'll make a new directory first.
And I'll just call it refining selection.
You don't have to do this, but I'm getting a little messy in here, so I want to make a new file in
that directory, and I'll just call it distinct dot SQL.
The most important part.
Okay, so in here right now, we have our select distinct author l name from books.
So let's start off by just running this.
Make sure that our file is working.
One thing I will point out, I don't recommend putting spaces in your directory names.
It's just not a great idea.
So I am going to edit that.
It won't break anything.
We can still do it, but it's just not great.
So let's just do like that refining election.
Well, that might work too, but refining selections.
All righty.
So if we want to run it, it's a source refining underscore selections slash distinct dot SQL.
Semicolon.
And it works.
So back to the problem at hand.
How do we actually get just distinct authors, first name and last name?
So what we could do, I'll comment this out for now is a select and then we can contact author first
name.
And Arthur last name.
Just like that from books.
And if we save and run that, now we get this.
And if we try and do distinct on that whole thing.
That works.
We get Dan Harris and Frieda Harris are distinct and we only have one Neil Gaiman, one Dave Eggers
and so on.
And of course, we could go add the space that we want there and try doing it again.
And that gives us distinct, full names like this.
That's one way, but there's actually an easier way and that's using distinct.
We just do a select distinct and if you do author we can do F name first or L name.
Just separate them by a comma.
And we tried running that now.
You see, we only get unique rows back.
So it's basically applying distinct to the entire row.
So in this case, it's basically saying, I want the combination of the first name and last name to
be distinct.
So that's why we end up with one Neil Gaiman, one Dave Eggers and one Dan Harris and one Frieda Harris.
So it works in that way.
The only difference here, aside from this being longer, is that here we're actually just combining
them and checking if it's distinct here.
SQL, Stewart or MySQL is doing that for us.
And it depends just how you want your results.
Print it out.
If you want them combined as a full name, then this method works better.
And if you just want to keep your data separate in two different columns, then this is much easier
and a little bit shorter.
All right.
So that's pretty much all there is to distinct at its core.
It's pretty simple, and it's powerful, too.
It's something we'll use quite a bit throughout the course.
As our data gets more complicated, we'll also have more duplicated data, which isn't a bad thing.
But sometimes when you're trying to understand it or work with it, you only want distinct data, so
you use to sync.
All right.
I should shut up.