Now than that and really like now then all is all right too much.
OK 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 you know or some authors of 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 Foster 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.
Select Distinct author name from books.
So let's try to go over here make some space and let's just start just to show you what I'm talking
about by select author with out the distinct from books just like that.
And as you can see you know we have Geymann who's in here twice.
Aggers is in there twice carvers in there twice.
Foster Wallace is in there twice.
And Harris is in there twice.
However there's something kind of weird about Harris.
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 to duplicate.
It's as simple as Select Distinct author 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 in one Aggers one Foster Wallace and one Harris.
And of course we could do that for other things so let's try it for years.
So we have select released year from books.
In C C there are some books that have duplicates.
So for example 2003 there's two 2003's at least I think there's multiple There's always 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 select distinct at least year from books our list is shorter and now we only have 1 2003.
So the reason I show it to you is not just to show you another way if you're another example but also
we're working with numbers here and that's important to remember that released year isn't it.
It's not a Vardar but stinks 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 L name from books.
When we did this remember that there's actually lots to author name and name.
So when we did this you know remember we have Neil game twice and Dave Eggers and so on.
But then there was Dan Harris and Frida 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 name from books it reduces them to just that distinct last
name which is what you would expect.
It only gives us Harris once which makes sense we're asking for 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 you know Dan Harris is distinct from
Frita Harris.
There are a couple of ways of doing it.
One you already know at this point you can use Concat.
So I'll show you that now what we could do is concat author f name and author L 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 as well the most important part.
OK.
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'm going to edit that.
It won't break anything.
We can still do it but it's just not great.
Let's just do like that.
Refinding elections well that might work too.
But refinding selections already.
So if you want to run it it's a source refining underscore selection's slash distinct dot as well.
Michael and it works OK.
So back to the problem at hand how do we actually get just distinct authors first name and last name
so we could do come at this out for now.
It's a select and then we can concat author first name
and author 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 Frita Harris are distinct and we only have one new game and one Dave
Eggers and so on.
And of course we could go ad the space that we want there and tried 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 name first or 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 new game in one.
Dave Eggers and one Dan Harris and one free to 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.
Sequel's do ambuscades doing that for us.
And it depends how you want your results printed out if you want them combined as a full name.
And 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 and it's chords 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 you're trying to understand
it or work with it you only want distinct data.
So you used to sinked.
All right.
I should shut up.