How to Limit Rows in a SQL Server?

To limit rows in SQL Server, use the TOP clause in the SELECT statement. Using the TOP clause in SQL Server, users can limit the number of rows in the results set.

Here, we will understand how to limit rows in SQL Server with the help of different examples.

Let’s check the steps to limit the number of rows on the SQL server. We will start by creating a table, then inserting records, and then using the TOP clause to limit rows.

Step 1: Creating the Database

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

Query:

CREATE DATABASE w3wikiDatabase;

Step 2: Using the Database

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

Query:

USE w3wikiDatabase;

Step 3: Create Table

Use the below SQL statement to create a table ‘Participant’

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'),
    (56, 'NN', 75, 'IT'),
    (57, 'RR', 100, 'CSE'),
    (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)

Step 5: Limit Rows Using TOP and ORDER BY

Use the TOP clause to limit rows in results set and ORDER BY statement to sort the results in ascending or descending order.

Syntax:

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 look at some SQL query examples on how to limit rows in SQL Server. We will use the above table in these examples.

Query 1:

This query limit the rows and returns only 2 rows.

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.