LIMIT

LIMIT gives us a way to limit the number of rows in the result

SELECT *
FROM cars
LIMIT 5;

`id` `INTEGER`	`make` `VARCHAR`	`model` `VARCHAR`	`year` `INTEGER`	`color` `VARCHAR`	`asking_price_usd` `INTEGER`	`miles_driven` `NUMERIC`	`is_paid_off` `BOOL`
10128	acura	mdx	2010	black	9800	97000.0	TRUE
53953	honda	cr-v	2006	black	3900	129853.7	TRUE
85215	mercedes-benz	c-class	2010	black	7250	165074.4	FALSE
76898	audi	a6	2007	black	4500	18754.1	TRUE
90175	hyundai	elantra	2012	gray	5600	112000.0	TRUE

This can be any 5 cars. We can't know which cars it will give us. So, LIMIT alone isn't always very meaningful, but it is useful for a quick glimpse of the data.

Top Row

What if we want to know which car in the cars table is the most expensive? For that, we can combine ORDER BY with LIMIT.

SELECT *
FROM cars
ORDER BY asking_price_usd DESC
LIMIT 1;

`id` `INTEGER`	`make` `VARCHAR`	`model` `VARCHAR`	`year` `INTEGER`	`color` `VARCHAR`	`asking_price_usd` `INTEGER`	`miles_driven` `NUMERIC`	`is_paid_off` `BOOL`
85027	honda	accord	2022	white	15990	300.0	FALSE

Because we ordered by asking_price_usd DESC, we know the most expensive car is in the top row. By adding LIMIT 1 we only get that row with the most expensive car. We know for sure it will be that particular car because of the use of ORDER BY.

Top n Rows

This particular pattern of getting the top 3, top 10, or top n of something is common, so ORDER BY and LIMIT are a dynamic duo. All you have to do is change LIMIT 1 to some other number.

Here is how we would get the 3 cars with the least mileage.

SELECT *
FROM cars
ORDER BY miles_driven
LIMIT 3;

`id` `INTEGER`	`make` `VARCHAR`	`model` `VARCHAR`	`year` `INTEGER`	`color` `VARCHAR`	`asking_price_usd` `INTEGER`	`miles_driven` `NUMERIC`	`is_paid_off` `BOOL`
85027	honda	accord	2022	white	15990	300.0	FALSE
84470	honda	civic	2006	white	6250	6000.0	TRUE
76898	audi	a6	2007	black	4500	18754.1	TRUE

Execution Order

We've learned several clauses at this point. Each of these transforms the table in some way. It helps to know what order in which these steps happen. It isn't the same order that they appear in the query itself. So does it do WHERE before or after LIMIT? Have a look at this diagram.

This figure only includes clauses we've covered so far. There are more to come.