Back to AGGREGATIONS & GROUP BY, ORDER BY

LIMIT AND ALIASING

Understand LIMIT Clause and aliasing

4 minutesVideo LessonPDF notes

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Ready to continue?

Mark this lesson as complete when you're ready to proceed.

Key moments

LIMIT basic function — The LIMIT clause specifies how many rows should be returned in the output.
Basic LIMIT demonstration — Running a query with LIMIT 3 returns only the first three rows of the result set.
LIMIT with ORDER BY — Combining LIMIT with ORDER BY DESC allows finding the top N values, such as the three oldest employees.
LIMIT offset parameter — The syntax LIMIT offset, count allows skipping the first 'offset' rows before returning 'count' rows.
Introduction to Aliasing — Aliasing is a way to temporarily change the name of a column in the output.
Aliasing necessity — When using aggregate functions, the resulting column name is often complex, making it difficult to reference in clauses like HAVING.
Using AS for Aliasing — The AS keyword is used to rename an aggregate column, allowing the new name to be used cleanly in the HAVING clause.
AS keyword optionality — The AS keyword is not strictly necessary for aliasing, as it is often implied by the SQL engine.

PDF notes

Bottom line: You will learn how to restrict the number of rows returned using LIMIT, including applying offsets. You will also master column aliasing using the AS keyword to rename aggregate function outputs, enabling cleaner use in clauses like HAVING.

This lesson on LIMIT AND ALIASING is hands-on and example-driven. You will learn how to restrict the number of rows returned using LIMIT, including applying offsets. You will also master column aliasing using the AS keyword to rename aggregate function outputs, enabling cleaner use in clauses like HAVING.

What You'll Be Able To Do

Restrict the number of rows returned by a query using LIMIT.
Identify the N largest or smallest values using LIMIT combined with ORDER BY.
Apply an offset parameter to skip a specified number of initial rows.
Rename a calculated column output using the AS keyword.
Reference an aliased aggregate column within a HAVING clause.

Topics Covered in LIMIT AND ALIASING

LIMIT basic function (0:00 - 0:15) — The LIMIT clause specifies how many rows should be returned in the output.
Basic LIMIT demonstration (0:15 - 0:30) — Running a query with LIMIT 3 returns only the first three rows of the result set.
LIMIT with ORDER BY (0:30 - 0:55) — Combining LIMIT with ORDER BY DESC allows finding the top N values, such as the three oldest employees.
LIMIT offset parameter (0:55 - 1:40) — The syntax LIMIT offset, count allows skipping the first 'offset' rows before returning 'count' rows.
Introduction to Aliasing (1:40 - 2:00) — Aliasing is a way to temporarily change the name of a column in the output.
Aliasing necessity (2:00 - 2:45) — When using aggregate functions, the resulting column name is often complex, making it difficult to reference in clauses like HAVING.
Using AS for Aliasing (2:45 - 3:30) — The AS keyword is used to rename an aggregate column, allowing the new name to be used cleanly in the HAVING clause.
AS keyword optionality (3:30 - 3:50) — The AS keyword is not strictly necessary for aliasing, as it is often implied by the SQL engine.

SQL Cheat Sheet

LIMIT N — Restricts the result set to the first N rows
```
SELECT * FROM employee_demographics LIMIT 3;
```
LIMIT offset, count — Skips 'offset' rows, then returns 'count' rows
```
SELECT * FROM employee_demographics LIMIT 2, 1;
```

ORDER BY <col> DESC LIMIT N — Finds the top N values based on the column

SELECT * FROM employee_demographics
ORDER BY age DESC LIMIT 3;

AVG(col) AS alias_name — Assigns a temporary name to a calculated column

SELECT gender, AVG(age) AS average_age
FROM employee_demographics GROUP BY gender;

HAVING alias_name > value — Filters grouped results using the aliased name

SELECT gender, AVG(age) AS average_age
FROM employee_demographics GROUP BY gender HAVING average_age > 40;

Comparison Table

LIMIT Usage	Syntax Pattern	Effect
Basic Restriction	LIMIT N	Returns the first N rows.
Offset Restriction	LIMIT offset, count	Skips 'offset' rows, returns 'count' rows.
Top N Filtering	ORDER BY col DESC LIMIT N	Returns N rows with highest values.

Common Pitfalls

Mistake: Forgetting LIMIT is applied after ORDER BY. Avoid: Always place ORDER BY before LIMIT to ensure you get the correct top/bottom N rows.
Mistake: Assuming the LIMIT offset parameter starts counting at 1. Avoid: Remember the offset parameter is zero-indexed (0 is the first row).
Mistake: Trying to use the alias in the WHERE clause. Avoid: Aliases are defined after WHERE; use the original function or column name.

FAQs

Is the AS keyword required for aliasing? No, AS is optional and often implied by the SQL engine. However, using AS is recommended for improved query readability and clarity.
Can I use LIMIT without ORDER BY? Yes, but the rows returned are arbitrary unless the table has a defined primary key order. Always use ORDER BY if you need specific rows (e.g., oldest, newest).
Where does LIMIT fit into the standard SQL query structure? LIMIT is almost always the very last clause executed in a SELECT statement, after filtering (WHERE), grouping (GROUP BY), and ordering (ORDER BY).

Common pitfalls

Mistake: Forgetting LIMIT is applied after ORDER BY. Avoid: Always place ORDER BY before LIMIT to ensure you get the correct top/bottom N rows.
Mistake: Assuming the LIMIT offset parameter starts counting at 1. Avoid: Remember the offset parameter is zero-indexed (0 is the first row).
Mistake: Trying to use the alias in the WHERE clause. Avoid: Aliases are defined after WHERE; use the original function or column name.

Hello everybody, in this lesson, we're going to be taking a look at limit and aliasing. Limit is just going to specify how many rows you want in your output. If we take this table for example, if we come right here and we say limit, let's do three. If we run this, it's only going to take the top three that we have. Let's go ahead and run this. As you can see, we have employee one, three and four, Leslie, Tom, and April. Now, this seems super straightforward, really, really easy, but it can be combined with order by to actually be really powerful. For example, let's say we wanted to take the three oldest employees. All we'd have to do is come right under here, and we say order by, and we'll order by the age in descending order. So we're going to order on age descending and then it's going to take the top three. So if we run this, and very quickly, we have the top three oldest people in this table. Now there is one additional parameter that we can use in limit, and all we have to do to access it is have a comma here. Now, what this is going to do, and I'll put a one here. What this is going to do is it's now going to say, we're going to start at position three, and then we're going to go one row after it. Now I actually want to take one of these people. So let's start at position two and select the next one after it, which should be Leslie Nope. So we're going to start at position two, and we're going to select the one right after it. So we're going to start at position two, and then one means we're taking the next one row. Let's go ahead and run this. And as you can see, we got Leslie Nope in our output. Now let's come right down here. We are going to now look at aliasing. Now, aliasing is just a way to change the name of the column, for the most part. And it can also be used in joins, but we're going to take a look at joins or aliasing joins in the intermediate series. In a previous lesson, we looked at a group by that looked like this. We select the gender, then we said from, I believe it was employee_demographics. Then we said group by gender, and we also had the average, and I think it was age. There we go. And we'll add our semicolon. Let's go ahead and run this. In our output, we have gender as our gender column, the same as the column name, but then average age is average age. And so if we want to actually do something like a having, where we say having the average age, let's say greater than 40, like we had it, we have to actually use this aggregate function in our having clause, and we don't want to always have to do that. We can actually change the name of this column and subsequently use it throughout our query with that aliased name. So I'm going to say as, and that's the keyword to actually change it. We'll say as, and we'll do average_age. So now we've changed this name to average_age, and we can come down here to having and say having the average age greater than 40. And when we run this, it works perfectly, and you'll notice that the name of the column was actually changed. Now, this as isn't actually 100% needed. It's kind of implied, even if we get rid of it, it's implied there's like this as in there somewhere. Um, but we don't have to have it. If we took it out and ran it like this, it would still work exactly the same. So that is how we can use limit and aliasing in SQL. And congratulations, this is the end of the beginner series in MySQL. In the intermediate series, we're going to take a look at things like joins, unions, case statements, subqueries, and window functions.

Frequently asked questions

Is the AS keyword required for aliasing?

No, AS is optional and often implied by the SQL engine. However, using AS is recommended for improved query readability and clarity.

Can I use LIMIT without ORDER BY?

Yes, but the rows returned are arbitrary unless the table has a defined primary key order. Always use ORDER BY if you need specific rows (e.g., oldest, newest).

Where does LIMIT fit into the standard SQL query structure?

LIMIT is almost always the very last clause executed in a SELECT statement, after filtering (WHERE), grouping (GROUP BY), and ordering (ORDER BY).