A SELECT TOP n statement returns the first n rows.
SELECT TOP is useful when working with large datasets.
Other databases use keywords like LIMIT, OFFSET, and ROWNUM.
List the top 5 largest orders, sorted by amount.
SELECT TOP 5 *
FROM [Order]
ORDER BY TotalAmount DESC
| Id | OrderDate | OrderNumber | CustomerId | TotalAmount |
|---|---|---|---|---|
| 618 | 2014-02-02 00:00:00.000 | 542995 | 63 | 17250.00 |
| 783 | 2014-04-17 00:00:00.000 | 543160 | 71 | 16321.90 |
| 734 | 2014-03-27 00:00:00.000 | 543111 | 34 | 15810.00 |
| 125 | 2012-12-04 00:00:00.000 | 542502 | 62 | 12281.20 |
| 177 | 2013-01-23 00:00:00.000 | 542554 | 51 | 11493.20 |
TOP syntax.
SELECT TOP n column-names FROM table-name
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
SELECT TOP 10 Id, ProductName, UnitPrice, Package
FROM Product
ORDER BY UnitPrice DESC
| Id | ProductName | UnitPrice | Package |
|---|---|---|---|
| 38 | Côte de Blaye | 263.50 | 12 - 75 cl bottles |
| 29 | Thüringer Rostbratwurst | 123.79 | 50 bags x 30 sausgs. |
| 9 | Mishi Kobe Niku | 97.00 | 18 - 500 g pkgs. |
| 20 | Sir Rodney's Marmalade | 81.00 | 30 gift boxes |
| 18 | Carnarvon Tigers | 62.50 | 16 kg pkg. |
| 59 | Raclette Courdavault | 55.00 | 5 kg pkg. |
| 51 | Manjimup Dried Apples | 53.00 | 50 - 300 g pkgs. |
| 62 | Tarte au sucre | 49.30 | 48 pies |
| 43 | Ipoh Coffee | 46.00 | 16 - 500 g tins |
| 28 | Rössle Sauerkraut | 45.60 | 25 - 825 g cans |
