Module 2: Limiting Data With TOP Clause

Previous Tutorial - Next Tutorial

As we have studied in T-SQL you can filter the records using the WHERE clause, but further, you can also limit the rows returned in a query result set to a specified number of rows or a percentage of rows in SQL Server by using the TOP clause.

This is the general syntax of the TOP clause. Here keyword defined in square brackets is optional, but it has altogether a different meaning in terms of functionalities. We will go through each of them in a few minutes. With the TOP clause, you can filter and request a number or a percentage of rows from the query results set based on a specified ORDER by clause.

You can specify the top option in the SELECT clause, followed by the requested number of rows. Let's open the SQL server management studio and see how this TOP clause works.

Here we will again use the employee table to understand the TOP clause. Now when I execute this query, it will give me the total records on this employee table, it is 290 records. My requirement is I want to return only the first 5 records from this table.

After the SELECT clause, I need to specify the TOP clause. Now I want to fetch the first five rows, so for that, I need to write a query and when I execute the query, then it will give me the first 5 records from the employee table.

Now the same way you can also specify a percentage of rows to return instead of the number with this clause. To do that, specify a value in the range from zero to a hundred in the parentheses and the keyword person after the TOP clause. In the tooltip, you can see how you can specify the TOP clause with the percent option in the SELECT statement.

Now over here, let me remove the TOP option and when I execute this query, it will give me 290 records. Now I want to fetch the top 50% records from the table. Let's see how we can specify the top 50 percent in this SELECT statement and for that, I need to write a query, when I execute the query it will give me 145 records which are half of the total record of this table. Now if I specify the top 1 percent instead of 50 percent, then it will give me the record.

The percent option computes the ceiling value of the resulting number of rows if it's a float value. In this example, the total number of rows in the table is 290. Filtering 1 percent gives me 2.9. The ceiling value of 2.9 is 3. Hence this query returns me three records.

Now here, please note that an ORDER BY clause is not mandatory when you use the TOP clause, but then there is no guarantee that the same rows will be returned if you re-run the query again and again. Now let's move on to another concept, which is with ties option.

In SQL server when we use this option with ties, the additional rows will be included if their value matches or tie with the value of the last row. To use this clause, you must have to use the ORDER by clause. As you can see in the tooltip, this is the general syntax of the with ties option. Now, let us understand from one simple example of how this clause works.

Now, to use them with the tie option, let me add the ORDER BY clause in this query. If I execute this query, I will get the records based on this book title order. For example, if I specify the TOP 6 and execute this query, then it will give me the 6 records. But now when I use them with the tie option and execute this query, it will give me the 8 records. So why does it return 8 rows instead of 6?

To understand that, let us remove the TOP option and let us execute this query again. As I said, when you specify the with ties option, then it will include some additional rows if their value ties with the value of the last row. So in this case, if you specify the top 6, then the last value becomes this (363923697) record.

But if you notice that the additional rows, which are rows number 7 and rows number 8, also have the same job title. When I specify with ties option, it will give me the 8 records instead of 6. Now, once again, let me remind you one note that you can specify the TOP clause with the WITH TIES argument in the SELECT statement.

In this case, you just need to specify the ORDER by clause. If you want to use with ties option, then it is compulsory to use the ORDER BY clause. This is the end of Module 2. Before we go to module 3, let us summarize what we have learned in this module.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials:

• General SQL Introduction
• Module 1: Getting Started with SQL Server
• Module 1: SQL Server Management
• Module 1: Essential SQL Commands
• Module 1: SQL Introduction Essentials
• Module 2: Learning SQL Overview
• Module 2: Logical Query Processing Order
• Module 2: Select Statement Fundamentals
• Module 2: Filtering Data with WHERE Clause Overview
• Module 2: Filter Data with WHERE Clause - Part 1
• Module 2: How to Filter Data with WHERE Clause - Part 2
• Module 2: Filter PDF Data Using WHERE clause - Part 3
• Module 2: Sorting Data Using ORDER BY Clause
• Module 2: Grouping Data with GROUP BY Clause Part 1
• Module 2: Grouping Data with GROUP BY Clause Part 2
• Module 2: Grouping Data with GROUP BY Clause Part 3
• Module 2: Limiting Data With TOP Clause
• Module 2: Summary and Quiz
• Module 3: SQL Joins and SET Operators
• Module 3: Understanding Inner Join
• Module 3: Understanding Outer Join
• Module 3: Cross Join Explained
• Module 3: Self Join Explained
• Module 3: UNION and UNION ALL
• Module 3: INTERSECT and EXCEPT
• Module 3: Understanding Sub-Queries
• SQL Analytic Functions