Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

How to Limit Rows in a SQL Server?

  • Difficulty Level : Hard
  • Last Updated : 21 Sep, 2021

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“.

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

Step 1: Creating the Database

Use the below SQL statement to create a database called GeeksForGeeksDatabase.

Query:



CREATE DATABASE GeeksForGeeksDatabase;

Step 2: Using the Database

Use the below SQL statement to switch the database context to GeeksForGeeksDatabase.

Query:

USE GeeksForGeeksDatabase;

Step 3: Table Definition

Query:

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

Query:

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:



Query:

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 :

Query:

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.

Query 1:

SELECT TOP(2) *
FROM Participant
ORDER BY Percentage DESC;

Output:



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 :

Query 2:

SELECT TOP(2) *
FROM Participant
WHERE ID != 58
ORDER BY Percentage;

Output:

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.

 

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!