How to Use the Limit Statement in SQL

SQL Tutorials 7 min read
limit statement in sql
Blog / SQL Tutorials / How to Use the Limit Statement in SQL

How to Use the Limit Statement in SQL?

Whenever we want to avoid the flood of more information than what we need, the LIMIT statement in SQL can help us out. However, we will be using keywords such as HAVING and WHERE, so if you have no idea what is the difference between the two, check out the linked tutorial.

Side note: We will be using the ‘employees’ database, so make sure you have it on your computer.

woman hands using laptop, limit statement in sql

Let’s select all rows from the “Salaries” table. We can do that by typing:

SELECT

               *

FROM

               salaries;

Once we do that, we’ll obtain a list with 1,000 rows because this is the default maximum number of rows displayed in a result-set in Workbench.

default maximum rows, limit statement in sqlThis default limit value can be lifted, however. Here is how we do that.

data science training

How to Increase the Limit?

First, let’s expand the Edit tab from the main menu of the interface. Then, select “Preferences”.

preferences, limit statement in sql

On the left-hand side of the window that appears, you’ll have the “SQL Execution” option in the “SQL Editor” section.

SQL Editor, limit statement in sql

Click on it. Then, you can untick the “limit rows” box or adjust the “Limit Rows Count” as you like.

Let’s lift the limit completely by unticking the box.

Select query results, limit statement in sqlPress “OK” and then re-run the same query.967330 rows returned, limit statement in sql
SQL took more time to deliver the results. Furthermore, we obtained 967,330 records! Not much less than a million.

How to Decrease the Number of Records?

Do you always need lists that long? Absolutely not. Here is an example of how to avoid seeing so many records in your output.

Imagine your boss just told you, “Please show me the employee numbers of the 10 highest paid employees in the entire database.”

First, when you understand you are looking for top performers, as shown by a certain statistic, immediately think of sorting the output in descending order.

10 highest paid employees, limit statement in sql

Namely, such is the case here- we will order all individuals on our list according to their salary.

Writing the Code

For instance, to this short query, we could add the phrase:

ORDER BY salary DESC;

And you will obtain a list with the highest paid individuals on top.

highest paid employees, limit statement in sql

However, this list was huge, too. Adjusting the limit rows count from the “Preferences” window of the Edit tab affects any query you run from your SQL editor. Often, you might prefer setting a limit to the output of a specific query in this way.

How to Implement the LIMIT Statement in SQL?

We can add a LIMIT statement at the end, just before the semicolon. It is always succeeded by an integer value indicating how many records must be displayed in our result.

Your boss asked about the 10 highest paid employees.

Therefore, LIMIT 10 will do the trick.10 rows returned, limit statement in sql

This was a much quicker query, and it answered our problem directly.

Side note: If you order the statement by another column, the resulted list will be different.

Let’s substitute the “salary” column with “emp_no”.

So, now the code will look like this:

SELECT

               *

FROM

               salaries

ORDER BY emp_no DESC

LIMIT 10;

emp_no, limit statement in sql

The resulting 10 records were sorted by their employee number. They are not the highest paid individuals in the database.

We can infer that we must be prudent with the column we are ordering our input by, especially when adding the LIMIT statement at the end.

The Syntax

Ergo, putting the LIMIT statement at the end of your query is the syntax we must comply with, no matter how long the query is.

limit number

For instance, think of a task where we have to extract the number of distinct names of people hired after the 1st of January 1999. We can achieve that by writing:

SELECT

               first_name, COUNT(first_name) AS names_count

FROM

               employees

WHERE

               hire_date > ‘1999-01-01’

GROUP BY first_name

HAVING COUNT(first_name) < 200

ORDER BY first_name DESC;

If we must limit that output to 100 rows, we must add “LIMIT 100” at the end and the result will be satisfactory.

100 rows returned

Is There Another Way of Setting a Maximum Number of Records?

Finally, if you don’t want to use the LIMIT statement in SQL, there is a third way to set a limit. Just expand the little list at the top of the SQL query editor and select a limit count according to your preference.

limit to 100 rows

Unlike the first two options, this one sets no default limit or a limit belonging to a specific query only. It will be applied to the queries you run at this moment, unless a LIMIT clause designating a different limit rows count has been used.50 rows returned

Limited Ways of Creating a Limit

To sum up, you have 3 options from which you can choose how to limit your output. One of them includes the LIMIT statement in SQL which might be the easiest to use for some of you. For the others, who want to use their mouse a bit more, they can set a limit for all of their queries or only for the current one.

So, how would you feel if I explained to you how to join tables in SQL? If you are interested in the subject, then you will love learning about JOINs in SQL.

***

Eager to hone your SQL skills? Learn how to put theory into practice with our hands-on tutorials!

Next Tutorial: Intro to SQL Joins

Earn your Data Science Degree

Expert instructions, unmatched support and a verified certificate upon completion!

Leave a Reply

Your email address will not be published.

You have Successfully Subscribed!

×
Learn Data Science
this Summer!
Get 50% OFF