{"id":12368,"date":"2020-12-17T09:27:25","date_gmt":"2020-12-17T17:27:25","guid":{"rendered":"https:\/\/careerkarma.com\/blog\/?p=12368"},"modified":"2023-12-01T04:06:12","modified_gmt":"2023-12-01T12:06:12","slug":"sql-limit","status":"publish","type":"post","link":"https:\/\/careerkarma.com\/blog\/sql-limit\/","title":{"rendered":"SQL Limit: A Beginner’s Guide"},"content":{"rendered":"\n

The SQL LIMIT statement restricts how many rows a query returns. A LIMIT statement appears at the end of a query, after any ORDER BY statements. You can start a LIMIT statement at a particular row using the offset argument.<\/em><\/p>\n\n\n\n

When you\u2019re working in SQL, you may only want to retrieve a specific number of rows from a query.<\/p>\n\n\n\n

For example, you may want to retrieve the top three employees based on the number of \u201cemployee of the month\u201d awards they have earned. This is easier to read than a list of all employees and how many “employee of the month” awards they have earned.<\/p>\n\n\n\n

There is a built-in SQL function that allows you to perform this action: SQL LIMIT. Limit allows you to limit the number of records a query to a certain amount.<\/p>\n\n\n\n

In this tutorial, we are going to discuss how to use the SQL LIMIT command. We’ll also discuss how to use TOP, the equivalent of LIMIT on SQL Server instances.<\/p>\n\n\n\n

Query Refresher<\/h2>\n\n\n\n

To retrieve information from a database, you have to write SQL queries. Queries almost always begin with the SELECT statement. We use this statement to tell the database which columns we want to retrieve. Queries often include the FROM term, which is used to state which table the statement will get its data from.<\/p>\n\n\n\n

Here is the syntax for an SQL query:<\/p>\n\n\n\n

SELECT column_name FROM table_name WHERE conditions_apply;<\/pre><\/div>\n\n\n\n
Here is an example of an SQL query that will return a list of the names of all employees in the employees<\/em> table:<\/p>\n\n\n\n
SELECT name FROM employees;<\/pre><\/div>\n\n\n\n
Our query returns the following: <\/p>\n\n\n\n
name<\/td><\/tr>
Luke
Mike
Hannah
Geoff
Alexis
Emma
Jonah
Adam<\/td><\/tr><\/tbody><\/table>\n\n\n\n
(8 rows)<\/p>\n\n\n\n
What is SQL LIMIT?<\/h2>\n\n\n\n
The SQL LIMIT clause restricts how many rows are returned from a query. The syntax for the LIMIT clause is: SELECT * FROM table LIMIT X;. X represents how many records you want to retrieve.<\/p>\n\n\n\n
For example, you can use the LIMIT clause to retrieve the top five players on a leaderboard. Or, you could use this clause the top five customers who have ordered the most products.<\/p>\n\n\n\n
Here\u2019s the syntax for a query that uses the LIMIT<\/em> clause:<\/p>\n\n\n\n
SELECT column_name FROM table_name LIMIT number_to_limit;<\/pre><\/div>\n\n\n\n
The LIMIT clause is only compatible with the SQL SELECT<\/a> statement. You cannot use a LIMIT clause on an SQL UPDATE<\/a> statement, for instance. Or you could not use a LIMIT clause on an SQL DROP or SQL ALTER TABLE statement.<\/p>\n\n\n\n
Your LIMIT number must be positive. Say you want to retrieve records from the bottom of the list. You should use the SQL ORDER BY<\/a> statement to order them in descending order. Then, you should use a LIMIT statement:<\/p>\n\n\n\n
SELECT * FROM table ORDER BY column DESC LIMIT X;<\/pre><\/div>\n\n\n\n
This is the syntax for ordering a table in descending order and limiting the output of the query. We’ve used an ORDER BY clause to retrieve only the first X records.<\/p>\n\n\n\n
SQL LIMIT Rows Example<\/h2>\n\n\n\n
Let\u2019s use an example to illustrate the SQL LIMIT<\/em> clause in action. Say that we want to get a list of the names of the top three longest-serving employees within our organization.<\/p>\n\n\n\n
We also want to retrieve the dates they were hired. We could use the following query to retrieve that information:<\/p>\n\n\n\n
SELECT name, hired_date FROM employees ORDER BY hired_date DESC LIMIT 3;<\/pre><\/div>\n\n\n\n
Our query returns the following:<\/p>\n\n\n\n
name<\/td>hired_date<\/td><\/tr>
Alexis<\/td>2014-04-01<\/td><\/tr>
Geoff<\/td>2012-03-17<\/td><\/tr>
Hannah<\/td>2011-09-30<\/td><\/tr><\/tbody><\/table>\n\n\n\n
(3 rows)<\/p>\n\n\n\n
Our query has returned the top three longest-serving employees by the date on which they were hired.<\/p>\n\n\n\n
Offset the SQL LIMIT Clause<\/h2>\n\n\n\n
If you want to offset the LIMIT<\/em> clause\u2014change the starting point\u2014you can specify a second parameter. Here is the syntax for an SQL LIMIT<\/em> query that offsets a query:<\/p>\n\n\n\n
SELECT column_name FROM table_name LIMIT starting_point, rows_to_return;<\/pre><\/div>\n\n\n\n
The starting point is the offset for the query (where your results will start). The “rows_to_return” is how many rows you want your query to retrieve.<\/p>\n\n\n\n
Let\u2019s say that we want to get the name of the fifth-highest paid employee. We could use the LIMIT<\/em> clause to specify that we want to get only one record, which should be the fifth highest-paid employee. Here\u2019s an example of such a query:<\/p>\n\n\n\n
SELECT name FROM employees ORDER BY salary DESC LIMIT 4, 1;<\/pre><\/div>\n\n\n\n
We specify that we want to get the item which comes fifth in our list\u2014remember, the first row is. We only want to get one record. Here\u2019s the result of our query:<\/p>\n\n\n\n
name<\/td><\/tr>
Alexis<\/td><\/tr><\/tbody><\/table>\n\n\n\n
(1 row)<\/p>\n\n\n\n
The offset clause only works in MySQL<\/em>. If you want to run an offset operation in PostgreSQL<\/em>, you\u2019ll need to use the OFFSET<\/em> operator like so:<\/p>\n\n\n\n
SELECT name FROM employees ORDER BY salary DESC OFFSET 4 LIMIT 1;<\/pre><\/div>\n\n\n\n
SQL TOP<\/h2>\n\n\n\n
In SQL Server and MS Access the SQL TOP<\/em> clause is used to limit the number of rows returned by a query. Here is the syntax for an SQL query that uses TOP:<\/p>\n\n\n\n
SELECT TOP number column_names FROM table_name;<\/pre><\/div>\n\n\n\n
Here\u2019s an example of a query that will return the top two highest-paid employees from the employees<\/em> table:<\/p>\n\n\n\n
SELECT TOP 2 * FROM employees ORDER BY salary DESC;<\/pre><\/div>\n\n\n\n
Our query returns the following: <\/p>\n\n\n\n
name<\/td><\/tr>
Jonah
Emma<\/td><\/tr><\/tbody><\/table>\n\n\n\n
(2 rows)<\/p>\n\n\n\n
The TOP<\/em> clause can be followed by the PERCENT<\/em> keyword, which retrieves the percentage of rows instead of the number of fixed rows. So, if you want to get the top 25 percent of employees based on their salaries, you could use this query:<\/p>\n\n\n\n
SELECT TOP 25 PERCENT * FROM employees\nORDER BY salary DESC;<\/pre><\/div>\n\n\n\n
Our query on the database with eight records returns the following:<\/p>\n\n\n\n
name<\/td><\/tr>
Jonah
Emma<\/td><\/tr><\/tbody><\/table>\n\n\n\n
(2 rows)<\/p>\n\n\n\n