TOP and ORDER BY

Sort a query's results with ORDER BY, ascending or descending, by one column or several, and keep only the first rows with TOP.

Novice · T-SQL · SQL · Updated

After this lesson you can

  • Sort results with ORDER BY and keep only the first rows with TOP

Key takeaways

  • Rows have no guaranteed order, so use ORDER BY whenever the order matters.
  • ORDER BY sorts ascending unless you add DESC; with several columns, rows that tie on the first are sorted by the next.
  • TOP (5) keeps only the first five rows. Always pair it with ORDER BY, or you get some five rows, not any particular five.
  • You can sort by an alias defined in the SELECT, such as StockValue.

Rows in a table have no guaranteed order. Without instructions, the database returns them however is quickest, and that can change from one run to the next. When the order matters, say so with ORDER BY.

Sorting

ORDER BY comes last in the query. It sorts ascending (smallest first, A to Z) unless you add DESC for descending:

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;
ProductName UnitPrice
Côte de Blaye 263.50
Thüringer Rostbratwurst 123.79
Mishi Kobe Niku 97.00
Sir Rodney's Marmalade 81.00

ASC means ascending; it's the default, so you rarely need to write it.

Sorting by more than one column

List several columns, separated by commas. Rows are sorted by the first; rows that tie on it are sorted by the second, and so on:

SELECT CompanyName, City, Country
FROM Customers
ORDER BY Country, City;
CompanyName City Country
Cactus Comidas para llevar Buenos Aires Argentina
Océano Atlántico Ltda. Buenos Aires Argentina
Rancho grande Buenos Aires Argentina

Each column can have its own direction: ORDER BY Country, City DESC.

Only the first rows: TOP

TOP goes right after SELECT, and returns only the first few rows of the result. With ORDER BY, it answers "top five" questions:

SELECT TOP (5) ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;
ProductName UnitPrice
Côte de Blaye 263.50
Thüringer Rostbratwurst 123.79
Mishi Kobe Niku 97.00
Sir Rodney's Marmalade 81.00
Carnarvon Tigers 62.50

TOP without ORDER BY gives you some five rows, not any particular five. Always pair them.

The parentheses in TOP (5) are optional for a plain number (TOP 5 works too), but they're required when the number comes from a variable, so it's a good habit.

Sorting by a calculated column

You can sort by an alias you defined in the SELECT:

SELECT TOP (3) ProductName
    , UnitPrice * UnitsInStock AS StockValue
FROM Products
ORDER BY StockValue DESC;

Check yourself

2 questions to try on the lesson page.

So what

ORDER BY makes results predictable, and TOP with ORDER BY answers the "best", "biggest" and "latest" questions every app asks.

Key terms

Cheat sheets