{"id":731,"date":"2018-07-31T15:23:32","date_gmt":"2018-07-31T08:23:32","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=731"},"modified":"2024-09-24T20:59:37","modified_gmt":"2024-09-24T13:59:37","slug":"sql-server-select-top","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-select-top\/","title":{"rendered":"SQL Server SELECT TOP"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server SELECT TOP<\/code> statement to limit the rows returned by a query.<\/p>\n\n\n\n

Introduction to SQL Server SELECT TOP #<\/a><\/h2>\n\n\n\n

The SELECT TOP<\/code> clause allows you to limit the rows or percentage of rows returned by a query. It is useful when you want to retrieve a specific number of rows from a large table.<\/p>\n\n\n\n

Since the order of rows stored in a table is unspecified<\/em>, the SELECT TOP<\/code> statement should always be used with the ORDER BY<\/a><\/code> clause. This ensures the result set is limited to the first N<\/code> number of ordered rows.<\/p>\n\n\n\n

The following shows the syntax of the TOP<\/code> clause with the SELECT<\/code> statement:<\/p>\n\n\n

SELECT<\/span> TOP (expression) [PERCENT<\/span>]\n    [WITH<\/span> TIES<\/span>]\nFROM<\/span> \n    table_name\nORDER<\/span> BY<\/span> \n    column_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
expression #<\/a><\/h3>\n\n\n\n
Following the TOP<\/code> keyword is an expression that specifies the number of rows to be returned. The expression is evaluated to a float value if PERCENT<\/code> is used, otherwise, it is converted to a BIGINT<\/a><\/code> value.<\/p>\n\n\n\n
PERCENT #<\/a><\/h3>\n\n\n\n
The PERCENT<\/code> keyword indicates that the query returns the first N<\/code> percentage of rows, where N<\/code> is the result of the expression<\/code>.<\/p>\n\n\n\n
 WITH TIES #<\/a><\/h3>\n\n\n\n
The WITH TIES<\/code> allows you to return additional rows with values that match those of the last row in the limited result set. Note that WITH TIES<\/code> may result in more rows being returned than specified in the expression.<\/p>\n\n\n\n
For example, if you want to return the most expensive product, you can use the TOP 1<\/code>. However, if two or more products have the same prices as the most expensive product, then you may miss the other most expensive products in the result set.<\/p>\n\n\n\n
To avoid this, you can use TOP 1 WITH TIES<\/code>. This will include not only the most expensive product but also other products that have the same highest price. By doing this, you won’t miss any equally expensive products in the result set.<\/p>\n\n\n\n
SQL Server SELECT TOP examples #<\/a><\/h2>\n\n\n\n
We will use the production.products<\/code> table in the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"products\"<\/figure>\n\n\n\n
1) Using SQL Server SELECT TOP with a constant value #<\/a><\/h3>\n\n\n\n
The following query uses SELECT TOP<\/code> with a constant to return the top 10 most expensive products from the production.products<\/code> table:<\/p>\n\n\n
SELECT<\/span> TOP 10<\/span>\n    product_name, \n    list_price\nFROM<\/span>\n    production.products\nORDER<\/span> BY<\/span> \n    list_price DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
2) Using SELECT TOP to return a percentage of rows #<\/a><\/h3>\n\n\n\n
The following example uses PERCENT<\/code> to specify the number of products returned in the result set. <\/p>\n\n\n\n
The production.products<\/code> table has 321<\/code> rows. Therefore, one percent of 321<\/code> is a fraction value ( 3.21<\/code>), SQL Server rounds it up to the next whole number, which is four ( 4<\/code>) in this case:<\/p>\n\n\n
SELECT<\/span> TOP 1<\/span> PERCENT<\/span>\n    product_name, \n    list_price\nFROM<\/span>\n    production.products\nORDER<\/span> BY<\/span> \n    list_price DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
3) Using SELECT TOP WITH TIES to include rows that match values in the last row #<\/a><\/h3>\n\n\n\n
The following query uses the SELECT TOP WITH TIES<\/code> to retrieve the top three most expensive products from the production.products<\/code> table:<\/p>\n\n\n
SELECT<\/span> TOP 3<\/span> WITH<\/span> TIES<\/span>\n    product_name, \n    list_price\nFROM<\/span>\n    production.products\nORDER<\/span> BY<\/span> \n    list_price DESC<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, the third most expensive product has a list price of 6499.99<\/code>. <\/p>\n\n\n\n
Because the statement uses TOP WITH TIES<\/code>, it returns three additional products with the same list prices as the third one.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the SQL Server SELECT TOP<\/code> statement to limit the number of rows or percentage of rows returned by a query.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
In this syntax, the SELECT<\/code> statement may include other clauses such as WHERE<\/a><\/code>, JOIN<\/a><\/code>, and GROUP BY<\/a><\/code> and HAVING<\/a><\/code>.<\/p>\n\n\n\n