How to Limit Rows in a SQL Server?
In this article, we will understand how to limit rows in SQL using different examples. We will see how to play with the SQL queries and get the resultant data according to different conditions and limitations.
For the purpose of demonstration, we will be creating a Participant table in a database called “GeeksForGeeksDatabase“.
Step 1: Creating the Database
Use the below SQL statement to create a database called GeeksForGeeksDatabase.
CREATE DATABASE GeeksForGeeksDatabase;
Step 2: Using the Database
Use the below SQL statement to switch the database context to GeeksForGeeksDatabase.
Step 3: Table Definition
CREATE TABLE Participant ( ID INTEGER PRIMARY KEY, Name TEXT NOT NULL, Percentage INTEGER NOT NULL, Branch TEXT NOT NULL );
Step 4: Insert data into 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');
You 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. To limit the resultant data for MS Access we use SELECT TOP syntax. LIMIT syntax works in MYSQL. In the Microsoft database, we use the below syntax (TOP which works similar to LIMIT in MYSQL)
Syntax of SELECT with TOP and ORDER BY statement in MySQL :
SELECT TOP(count) column1, column2, ...,column n FROM table_name [WHERE your conditions] [ORDER BY expression [ ASC | DESC ]];
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). Here count is the number of rows to be returned from the result.
Let’s understand this using some example queries.
SELECT TOP(2) * FROM Participant ORDER BY Percentage DESC;
Using the TOP query we found the 2 toppers participants from our table data having maximum percentage and do not want to use any conditional statements. ORDER BY Percent DESC has sorted the record in descending order and using LIMIT 2 we got the first 2 rows from the sorted result.
We can also include some situations using the WHERE clause in the above example. Suppose if we don’t want the ID 58 participant in our result set.
We can write queries like :
SELECT TOP(2) * FROM Participant WHERE ID != 58 ORDER BY Percentage;
The above query will select all the participants according to the imposed condition (i.e. all Participants except ID 58 participant will be selected) then the results would be sorted by Percentage 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 as we mentioned TOP(2).
This is how we can limit the records from tables in SQL using TOP. 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.