Back to cookbooks list Articles Cookbook

How to Limit Results in T-SQL

Problem:

You want to limit the number of rows resulting from a query in T-SQL.

Example:

In the exam table, there are names of the students with the results of the exam.

nameexam_result
Janet Morgen9
Taya Bain11
Anne Johnson11
Josh Kaur10
Ellen Thornton8

You want to get the three rows with the best exam results.

Solution:

SELECT TOP 3
  *
FROM exam
ORDER BY exam_result DESC;

The result of the query looks like this:

nameexam_result
Taya Bain11
Anne Johnson11
Josh Kaur10

Discussion:

After the SELECT keyword, use the TOP keyword along with the number of rows you'd like to retrieve (here, 3). Remember to sort the rows by the exam_result column in descending order using the ORDER BY clause and the DESC keyword.

SELECT TOP 3
  *
FROM exam
ORDER BY exam_result DESC;

If you'd like to retrieve three rows randomly instead of the best three, skip the ORDER BY part.

SELECT TOP 3
  *
FROM exam;

Of course, you can retrieve any number of rows you want. Just replace 3 with your desired number.

Recommended courses:

Recommended articles:

See also: