What if we set a LIMIT value that is greater than the total number of rows?

Frequently Asked Questions SQL FAQ
1

Question

In SQL, what happens if we set a LIMIT value that is greater than the total number of rows?

Answer

If the number set in the LIMIT clause surpasses the number of rows available to select, then it will just exclude the remaining amount of rows in the result set.

You might think of LIMIT as an upper bound for the number of rows to return, but not as a strict number of rows that must be returned.

Example

/* 
Say the table `name` has only 90 rows. 
Then, since 100 is greater than the number of rows, 
it will just return what rows are there. 
*/

SELECT *
FROM names
LIMIT 100;
11 Likes
2

What happens if there are multiple cells beyond the limit that share the same value? In the sample exercise for instance, if there were 3 movies with 8.8 rating. How would it decide which to show?

16 Likes
3

I’m not 100% certain, but I believe that in your situation without any other sorts or restrictions it would select the first three entries in the table that met your criteria based on the order they were originally entered into the table.

27 Likes
4

I think so as well, in the exercise I initially thought that ‘Inception’ and ‘Star Wars: Episode V’ were ordered that way because of alphabetical order. However, upon a second look, it would make a lot more sense that they were ordered by their id, which would confirm your point.

3 Likes
5

For the same values typically indeed it’s how they were inputed originally… but this might depend on language implementation. E.g. in python if I recall correctly a dictionary does not always return same positions of it’s elements as it does not have it fixed… so it’s related to the question how actually table data stored - more like lists or dictionaries without fixed position…

1 Like
6

If you query

SELECT *
FROM movies
ORDER BY id
LIMIT 3000;

You’ll get the same result as

SELECT *
FROM movies
ORDER BY id;

there’s no repeated or empty rows

1 Like
7

If there are multiple movies with the same rating that fall into the third place and you want to determine which movie specifically is selected, you can add additional columns to the ORDER BY clause to break the tie.

For example, if you want to order the movies with the same rating by the year of release in ascending order, you can modify the query like this:

sql code
SELECT name, year, imdb_rating
FROM movies
ORDER BY imdb_rating DESC, year ASC
LIMIT 3

This query will return the top 3 movies with the highest rating, and in case of ties in the rating, it will further sort the movies by the year of release in ascending order. This way, you can determine which movie is selected in the third place based on its release year.

Alternatively, you can also add a unique identifier or a secondary criterion to the ORDER BY clause to further break the tie and ensure that only one row is selected.

2 Likes
8

Can you combine the limit command with the distinct command? Like a limit to 3 distinct imdb_rating values?

10

on a related note, how do we find the top 3 rated movies for each year? e.g. top 3 rated for 2015, top 3 rated for 2014…2013…

11

Yes! The DISTINCT keyword can be used in any circumstance but the results will vary depending on selected columns and aggregated/grouped columns.

12

Hello @design7636512875.

While the solution to this is beyond the scope of this exercise, one of the simpler ways to do it is with the ROW_NUMBER() function and a subquery.

select * from (
  select row_number() over (partition by year order by imdb_rating desc) row_num,
  *
  from movies
  where imdb_rating is not null
  order by year desc, imdb_rating desc
)
where row_num < 4;

A Common Table Expression (CTE) could also be used and I believe would be more efficient for larger databases. And the RANK() function could work in place of the ROW_NUMBER() function.