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.
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.
How to Increase the Limit?
First, let’s expand the Edit tab from the main menu of the interface. Then, select “Preferences”.
On the left-hand side of the window that appears, you’ll have the “SQL Execution” option in the “SQL Editor” section.
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.
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.
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.
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.
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;
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.
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.
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 &amp;amp;gt; ‘1999-01-01’ GROUP BY first_name HAVING COUNT(first_name) &amp;amp;lt; 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.
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.
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.
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