SQL Server TOP Clause - SQL Server Guides

In this SQL Server tutorial, I will show you how to use the SQL Server TOP clause to get only a limited number of data in the query’s result set.

Where I will define ‘What is SQL Server Top Clause “, and with an example, I will explain how it works.

Also, you will learn two ways to use the TOP clause based on the number and percentage value. Additionally, I will explain the SQL Server TOP clause by applying it to the different tables to show you how it can be used.

Table of Contents

What is SQL Server TOP Clause?

Whenever you run the query on a table to retrieve the records, it returns a result containing many records. It depends on your table size; sometimes, you need a certain number of rows in your result set.

I mean, you want to limit the number of rows in your query result; let’s say you need only 10, 30, and 50, but not all the rows; in that case, you must use the SQL Server TOP clause. It is like the LIMIT clause in the other Relational Databases such as MySQL and PostgreSQL.

Using the TOP clause improves the query efficiency; now, the syntax for how to use the TOP clause in your SQL Server query is given.

SELECT TOP number|percent column_names
FROM table_name
WHERE condition
ORDER BY column_name ASC | DESC;

Where,

SELECT TOP: Specify the TOP clause after the SELECT statement to limit the number of rows.
number|percent: You can specify both the number of rows and the percent of rows that you want to include in your query result set.
column_name: Name the column you want to include in your result set.
WHERE conditions: Specifying the condition on which rows will be selected. It is optional.
ORDER BY column_name ASC | DESC: To specify the order of the rows in your result set, like you want to order the rows in ascending or descending order.

Let’s take an example to understand how to limit the number of records in a result set.

For example, you have a Sales table, as shown below.

Using the above table, you need only five records in your result set; for that, you can use the TOP clause.

Run the query below.

SELECT TOP 5 ProductName, SaleDate, SalesPrice FROM Sales;

Server TOP Clause Returning Five Rows in Result

As you can see in the result set, there are only 5 Products with ProductName, SalesDate, and SalesPrice. For example, Laptop Computer, Smartphone, LET TV, Digital Camera, and Wireless Mouse.

Instead of 5, you can get 10, 20 or even more of this number of rows in your result set. But as you can see, the order of rows is random; I mean, you can order the rows of the data in ascending (from low to high) and descending (from high to low).

In the query part, SELECT TOP 5 ProductName, SaleDate, SalesPrice FROM Sales; after the SELECT statement TOP 5 is specified, include only the five rows in the result set.

For example, you must find the top 3 products with the highest sales price. Here, you can use the ORDER BY clause as specified in the above syntax to order the rows of data in your result set, and the TOP clause will limit the rows to 2.

For example, look at the query below.

SELECT TOP 2 ProductName, SaleDate, SalesPrice 
FROM Sales
ORDER BY SalesPrice DESC;

Server TOP Clause Returning Two Rows in Result

In the result set, there are only two records: the top 2 products with the highest sale price, for example, Boya Mic and Laptop Computer, with a sales price of 1000.2394 and 9999.999 respectively.

Here in the query part ORDER BY SalesPrice DESC, order all the rows based on the SalesPrice column value in descending order (DESC), so the product with the highest sale price will be the first record, and the product with the lowest sales price will be the last records in the result set.

Then SELECT TOP 2 takes the first two records from the order rows data and includes them in the result set. This is how to apply the SQL Server TOP clause to limit the number of records in the query result set.

Using SQL Server TOP Clause with Percent Value

In the above section, you have to limit the number of rows by specifying the constant value with TOP clauses like TOP 5 and TOP 2, but you can specify the percent of rows that you want to include in the result.

Again, from the Sales table, you must include 10 per cent of rows in your result set. For that, you can use the query below.

SELECT TOP 10 PERCENT ProductName, SaleDate, SalesPrice 
FROM Sales;

Using SQL Server TOP Clause with Percent Value

From the query output, 10 per cent of rows are equal to 3 records, so, after the SELECT statement, the TOP 10 PERCENT is specified to limit rows to 10 percent in the query result. Here, you can specify any percentage like TOP 40 PERCENT, TOP 80 PERCENT, etc.

This is how to limit the number of rows in a result set based on the percentage value using the SQL Server TOP clause.

Using SQL Server TOP Clause with INSERT Statement

You can use the SQL Server TOP clause with an INSERT statement to limit the number of rows of data insertion in the table.

For example, here I will show you how to take certain records from one table and insert those records into the new table.

You have to create a table named ‘TopSalesPrice’ containing the top 10 product_ids, the product name, and the sales price. This information will be taken from the Sales table in the above sections.

Now, first, create a TopSalesPrice table using the query below.

CREATE TABLE TopSalesPrice (
product_id INT IDENTITY(1,1) PRIMARY KEY,
product_name VARCHAR(255),
product_sales_price DECIMAL(10,3) 
);

Use the query below to insert the top 10 products with product prices in TopSalesPrice.

INSERT INTO TopSalesPrice
     SELECT TOP 10 [ProductName],  
                   [SalesPrice]
     FROM Sales
     ORDER BY SalesPrice DESC;

View the TopSalesPrice using the query below.

SELECT * FROM TopSalesPrice;

Using SQL Server TOP Clause with INSERT Statement

From the output, the TopSalesPrice contains the top 10 products from the Sales table. If you separate the query into two parts, you will understand it.

INSERT INTO TopSalesPrice, and this part inserts the data into TopSalesPrice, but which data, the data which is returned by this part SELECT TOP 10 [ProductName],[SalesPrice] FROM Sales ORDER BY SalesPrice DESC.

This is how to insert the limited number of rows in a table using the SQL Server TOP clause with INSERT INTO statement.

Conclusion

In this SQL Server tutorial, you learned how to limit the number of rows in a query result set using the SQL Server TOP clause.

Then, you learned how to limit the number of rows based on the numeric and percentage values.

You also learned to insert a limited amount of data using the TOP clause with the INSERT INTO statement.