00:00Hello everybody, in this lesson we're going to be taking a look at limit and aliasing.
00:05Limit is just going to specify how many rows you want in your output.
00:08If we take this table for example, if we come right here and we say limit, let's do three.
00:14If we run this, it's only going to take the top three that we have.
00:19Let's go ahead and run this.
00:20As you can see we have employee one, three and four, Leslie, Tom and April.
00:25Now this seems super straightforward, really, really easy, but it can be combined with
00:29order by to actually be really powerful.
00:32For example, let's say we wanted to take the three oldest employees.
00:37All we'd have to do is come right under here and we say order by,
00:41and we'll order by the age in descending order.
00:45So we're going to order on age descending, and then it's going to take the top three.
00:49So if we run this, and very quickly we have the top three oldest people in this table.
00:55Now there is one additional parameter that we can use in limit,
00:58and all we have to do to access it is have a comma here.
01:02Now what this is going to do, and I'll put a one here.
01:05What this is going to do is it's now going to say we're going to start at position three,
01:09and then we're going to go one row after it.
01:13Now I actually want to take one of these people.
01:15So let's start at position two and select the next one after it, which should be Leslie Knope.
01:21So we're going to start at position two, and we're going to select the one right after it.
01:25So we're going to start at position two, and then one means we're taking the next one row.
01:30Let's go ahead and run this.
01:32And as you can see, we've got Leslie Knope in our output.
01:35Now let's come right down here.
01:37We are going to now look at aliasing.
01:42Now aliasing is just a way to change the name of the column for the most part.
01:48And it can also be used in joins, but we're going to take a look at joins,
01:52or aliasing joins in the intermediate series.
01:55In a previous lesson, we looked at a group by that looked like this.
01:58We selected gender, and we said from, I believe it was employee underscore demographics.
02:06Let me say group by gender.
02:09And we also had the average, and I think it was age.
02:14There we go.
02:15And we'll add our semicolon.
02:17Let's go ahead and run this.
02:18In our output, we have gender as our gender column, the same as the column name.
02:22But then average age is average age.
02:26And so if we want to actually do something like a having,
02:29where we say having the average age, let's say greater than 40, like we had it,
02:37we have to actually use this aggregate function in our having clause.
02:41And we don't want to always have to do that.
02:43We can actually change the name of this column and subsequently use it throughout our query
02:47with that aliased name.
02:49So I'm going to say as, and that's the keyword to actually change it.
02:53We'll say as, and we'll do average underscore age.
02:57So now we've changed this name to average underscore age, and we can come down here to
03:02having and say having the average age greater than 40.
03:07And when we run this, it works perfectly.
03:09And you'll notice that the name of the column was actually changed.
03:12Now, this as isn't actually 100% needed.
03:16It's kind of implied.
03:17Even if we get rid of it, it's implied there's like this as in there somewhere.
03:22But we don't have to have it.
03:24If we took it out and ran it like this, it would still work exactly the same.
03:28So that is how we can use limit and aliasing in SQL.
03:32And congratulations, this is the end of the beginner series in MySQL.
03:36In the intermediate series, we're going to take a look at things like joins,
03:40unions, unions, case statements, subqueries, and window functions.
Comments