How to select to in sql server

The SELECT TOP clause allows you to limit the number of rows or percentage of rows returned in a query result set.
Because the order of rows stored in a table is unspecified, the SELECT TOP statement is always used in conjunction with the order by clause. Therefore, the result set is limited to the first N number of ordered rows.
The following shows the syntax of the TOP clause with the SELECT statement:
In this syntax, the SELECT statement can have other clauses such as where, join, having , and group by.


Following the TOP keyword is an expression that specifies the number of rows to be returned. The expression is evaluated to a float value if PERCENT is used, otherwise, it is converted to a begin value.


The PERCENT keyword indicates that the query returns the first N percentage of rows, where N is the result of the expression.


The WITH TIES allows you to return more rows with values that match the last row in the limited result set. Note that WITH TIES may cause more rows to be returned than you specify in the expression.
For example, if you want to return the most expensive products, you can use the TOP 1. However, if two or more products have the same prices as the most expensive product, then you miss the other most expensive products in the result set.
To avoid this, you can use TOP 1 WITH TIES. It will include not only the first expensive product but also the second one, and so on.

SQL Server SELECT TOP examples

We will use the production.products table in the sample database for the demonstration.

1) Using TOP with a constant value

The following example uses a constant value to return the top 10 most expensive products.

2) Using TOP to return a percentage of rows

The following example uses PERCENT to specify the number of products returned in the result set. The production.products table has 321 rows, therefore, one percent of 321 is a fraction value ( 3.21), SQL Server rounds it up to the next whole number which is four ( 4) in this case.

3) Using TOP WITH TIES to include rows that match the values in the last row
The following statement returns the top three most expensive products:
The output is as follows:
In this example, the third expensive product has a list price of 6499.99. Because the statement used TOP WITH TIES, it returned three more products whose list prices are the same as the third one.
In this tutorial, you have learned how to use the SQL Server SELECT TOP statement to limit the number of rows or percentage of rows returned by a query.

Post a Comment