MySQL Database Tutorial - 7 - DISTINCT and LIMIT Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

what's going on guys welcome to your
seventh MySQL tutorial and in this
lesson we're going to be learning more
about MySQL pretty cool huh
so the first thing I want to tell you
guys is say you were in this scenario
you're sitting there working at your
desk and the boss comes in and says
Bucky I need you to give me a list of
all of the states our customers are from
so we say okay just give me an hour no
problem
select state from customers so we go
ahead and run this query an hour later
after taking an extended break we call
our boss back and say hey there you go
check it out
so he's reading through this lesson he's
like all right looks pretty good New
York North Carolina yadda yadda yadda
okay what the heck is this New York
again North Carolina
Bucky what are you doing you giving me a
wrong list that has like all these
duplicate and non distinct results what
did you just like copy this twice and
we're saying no our customers are
actually you know maybe we have two from
New York maybe we have three from North
Carolina and he's like dude ah because
evidently he calls me dude he's like no
I only want a unique list I don't want
to get results so we say oh you idiot
you should have told me that but I don't
say that out loud so in order to get a
list of unique or distinct results in
other words we only want each row to
display once even though we may have
additional customers for example four or
five customers from New York if this is
the case we only want to return it once
so in order to do that we use the
keyword distinct right before the column
names now whenever we go ahead and run
this query check it out we say okay
column back he checks our work again New
York North Carolina okay make sure they
didn't repeat and they did it excellent
so that's what we have to remember
whenever you want distinct or unique
results even though you may have a
duplicate result in your data use the
keyword distinct and it's only going to
return that row one time for each value
so now our boss is looking at all of
this in the same okay how come every
time you run a query it returns you know
like 100 results
we say well because that's how many rows
we have in our database but what have
you saying okay is there a way that we
can limit the number of results because
I don't want to be looking at a hundred
results every time I ask you a question
and we say whoa boss indeed there is so
in order to do that just go ahead and
run a select statement so say we wanted
to retrieve the ID and the name from
customers we just go ahead and copy this
and run it now whenever we were do this
it would return however many customers
were in our table so a goof I think our
table has like 100 customers in it so we
would go and we show this to our boss
and he's like dude I don't want that
many I only want the first five so in
order to limit your results use a
keyword called limit that's the
beautiful thing about MySQL it just
makes sense in everyday terms you don't
have to like translator or anything
whenever you want to limit your results
use the viewer limit makes sense so we
go ahead and show them this and he's
like ah I'm not bad so based on the
first column you select it takes that
criteria and basically we're saying okay
limit the first five people based on
their ID number so as you can see that
is why we got the first five people one
two three four five oh excuse me so now
we're saying okay that's nice and all
but what if I don't want to start at the
very beginning what if instead of you
know getting the first ten or twenty
customers I wanted to retrieve ten
customers but only I don't know maybe I
wanted to start with number six or
something well in order to do that we
need to use an additional variation of
limit so of course we know that whenever
we use limit with one number it
automatically starts at the very
beginning however there's also variation
where we can use it with two numbers so
let me run this query and talk to you
guys a little bit about this instead of
a limit five I use limit five comma 10
and hit go and now I can see that a
retrieved ten pieces of data starting
with number five and if you're saying
okay this actually starts at numbers
so what the heck is going on well first
let me talk to you a little bit about
how limit with two numbers works you can
use two number limit to specify a range
now whenever you're doing this the first
number is your starting point and the
second number is how many rows or how
many pieces of information do you want
to retrieve now remember why does it
start at six even though I put five here
the answer to that is because unlike
people where we start counting it 1 1 2
3 4 5 computers start counting at 0 0 1
2 3 4 and that is why our first result
is 6 instead of 5 so one last time
remember this limit with one value
whenever you hit something like limit 5
that always starts at the beginning
however if you want to do a range then
you need two values the first value is
your starting point and the second value
is how many pieces of information or how
many rows you want to retrieve so
basically you can use this variation to
start any will anywhere that you wanted
to so there you go there you have it
hopefully you guys understand limit now
because we're going to be using a lot in
the future and again if you guys have
any questions just go ahead and ask me
on my forum tnb forum com so again thank
you guys for watching don't forget
subscribe and I'll see you guys in the
next video