How to Limit Query Results in SQL?
In this article, we will learn how to limit query results in SQL using different examples. A MySQL supports the LIMIT clause to select a limited number of records. If we want to LIMIT the number of results that will return us a number of rows then we simply use the LIMIT command.
Step 1: Creating the Database
Use the below SQL statement to create a database called geeks:
CREATE DATABASE geeks;
Step 2: Using the Database
Use the below SQL statement to switch the database context to geeks:
Step 3: Table Definition
CREATE TABLE Participant ( ID INTEGER PRIMARY KEY, Name TEXT NOT NULL, Percent INTEGER NOT NULL, Branch TEXT NOT NULL );
Step 4: Adding data to the table
INSERT INTO Participant VALUES (55, 'BB',81 ,'Civil'); INSERT INTO Participant VALUES (56, 'NN',75 ,'IT'); INSERT INTO Participant VALUES (57, 'RR',100 ,'CSE'); INSERT INTO Participant VALUES (58, 'SS',94 ,'Civil');
We can use the below statement to see the contents of the created table:
SELECT * FROM Participant;
Now let’s see how to use limit query here. For this we use SELECT with LIMIT and ORDER BY statement in MySQL.
SELECT column1, column2, ... FROM table_name [WHERE conditions] [ORDER BY expression [ ASC | DESC ]] LIMIT count;
In the above syntax, WHERE conditions are optional conditions that must be true for the records to be selected. ORDER BY expression is an optional statement in the query used to return the result in either ascending or descending order according to the keyword (ASC or DESC). The LIMIT count is used with the SELECT statement to restrict the number of rows in the result set.
Let’s understand this using some example queries.
SELECT * FROM Participant ORDER BY Percent DESC LIMIT 2;
The LIMIT operator can be used in situations such as the above, where we need to find the top 2 participants having maximum percent and do not want to use any condition statements. ORDER BY Percent DESC has sort the record in descending order and using LIMIT 2 we got the first 2 rows from sorted result.
We can also include some situations using the WHERE clause in the above example. Suppose if we don’t want the Civil branch in our result set and want the first 2 Participants to have a low Percent.
We can write queries like :
SELECT * FROM Participant WHERE Branch != 'Civil' ORDER BY Percent LIMIT 2;
The above query will select all the participants according to the imposed condition (i.e. all Participants except Civil branch participants will be selected) then the results would be sorted by Percent in ascending order (The ORDER BY keyword sorts the records in ascending order by default). Finally, the first 2 rows would be returned by the above query.
This is how we can limit the records from tables in SQL. We can further play with the SQL queries and get the resultant data according to different conditions and limitations.
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.