SQL Limit Statement: How-To and Alternatives | 365 Data Science (2024)

Working with databases as opposed to spreadsheets presents unique challenges for managing the quantity and quality of our data. So, whenever we want to avoid the flood of more information than what we need, SQL’s limit statement can help us out.

In this tutorial, we will show you how to use this SQL function and discuss some workarounds to SQL Limits with their respective pros and cons. Additionally, we’ll be providing screenshots of the code and a step-by-step walkthrough of the solutions we’re implementing, so you can follow along without missing anything. Before we dive in, please bear in mind that we will be using keywords such as HAVING and WHERE, so if you need a more comprehensive overview of these and other statements then have a browse through our SQL tutorials.

In This Tutorial:

1. Getting Started with SQL Limits
2. How to Increase the Limit of Rows in SQL?
3. How to Decrease the Limit of Rows in SQL?
4. Writing the LIMIT SQL Code
5. How to Implement the Limit Statement in SQL?
6. The Syntax of LIMIT in SQL
7. More Ways to Implement an SQL Limit
8. Beyond SQL Limits

Getting Started with SQL Limits

Firstly, go ahead and install the latest version of SQL on your system. In addition, we will be using the ‘employees’database,so make sure you have it on yourcomputer as well.

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.

This default limit value can be lifted, however. Here is how we do that.

How to Increase the Limit on Rows in SQL?

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.

Press “OK” and then re-run the same query.

SQL took more time to deliver the results. Furthermore, we obtained 967,330 records! Not much less than a million.

How to Decrease the Limit on Rows in SQL?

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 LIMIT SQL 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.

Therefore, LIMIT 10 will do the trick.

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

Keep in mind, If you order the statement by another column, the resulting list will be different.

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

So, now the code will look like this:

SELECT *FROM salariesORDER BY emp_no DESCLIMIT 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 theLIMITstatement at the end.

The Syntax of LIMIT in SQL

Ergo, putting theLIMITstatement 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 1^st of January 1999. We can achieve that by writing:

SELECT first_name, COUNT(first_name) AS names_countFROM employeesWHERE hire_date > '1999-01-01'GROUP BY first_nameHAVING COUNT(first_name) < 200ORDER 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.

More Ways to Implement an SQL Limit

Finally, if you don’t want to use SQL’sLIMITstatement, there is a third way to set a limit. Just expand the little list at the top of the SQL query editor and select alimit countaccording 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.

Beyond SQL Limits

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

Enroll in our SQL course to enhance your skills. А strong grasp of SQL’s baseline functionalities will make all the difference once you start learning Tableau, and looking to integrate your SQL-managed database with this powerful data visualization tool. So, buckle up for our next tutorial on Joins where we will learn how to construct relationships between objects in SQL.

Q&As

1. What is SQL Limit?

SQL Limit is a function that allows you to retrieve a specific number of rows from a query. This is useful in cases where you only want to display an X number of entries from your database. Keep in mind that you can only use the LIMIT operator only on a query that begins with the SELECT statement. You can’t use it, for example, on a query containing the UPDATE, DROP, or ALTER TABLE statements. Moreover, your LIMIT number must be positive and no matter how long the query you’re limiting is, the LIMIT statement always comes at the end. Some practical examples of using the SQL limit statement are retrieving the highest-paid employees on a list or displaying the top-scoring players on a football team.

2. How Do You Write a Limit in SQL?

The basic syntax of an SQL Limit statement is as follows:

SELECT expressions

FROM tables

[WHERE conditions]

[ORDER BY expression [ ASC | DESC ]]

LIMIT number_rows

Let’s see it in action. Image we’re tasked with displaying the number of distinct names of people hired after the 1^stof January 1999 from a list of hundreds of names, employee numbers, salary figures, hiring dates and more. Moreover, we want to limit our result to 100 rows. Here’s how we do it:

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;

3. Does MySQL Have a Limit?

No, MySQL has no limit on the number of databases or tables. Technically, databases themselves have a limit on how many queries they can hold, but the numbers are usually too big to care. This is one crucial distinction between databases and spreadsheets like Excel or Google Sheets. However, tables themselves have a limit of 4096 columns and a row-size limit of 65, 535 bytes – even though this limit can be increased. Moreover, if you are working with integers, you can only have as many rows as the number of unique values in your query. You may also be faced with constraints on the size of files your operating system supports. As well as constraints stemming from the size of your hard drive.