# 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 November 15, 2026

By Gregor Dzierzon

## 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**:

```sql
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:

```sql
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:

```sql
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`:

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

## Do it: sort the price list by category, then price

The printed price list groups the products by category, and puts the most expensive product first within each category.

This box runs the SQL that every database shares. `TOP` is SQL Server's own word, so this Do It practices only the sorting.

The `Products` table (the **Tables** tab shows the SQL that makes it) has these rows:

| ProductName | CategoryId | UnitPrice |
|---|---|---|
| Teatime Chocolate Biscuits | 3 | 9.2 |
| Aniseed Syrup | 2 | 10 |
| Guaraná Fantástica | 1 | 4.5 |
| Pavlova | 3 | 17.45 |
| Chai | 1 | 18 |
| Chef Anton's Cajun Seasoning | 2 | 22 |
| Chang | 1 | 19 |
| Northwoods Cranberry Sauce | 2 | 40 |
| Sir Rodney's Marmalade | 3 | 81 |

Your query should give exactly these rows, in this order:

| ProductName | CategoryId | UnitPrice |
|---|---|---|
| Chang | 1 | 19 |
| Chai | 1 | 18 |
| Guaraná Fantástica | 1 | 4.5 |
| Northwoods Cranberry Sauce | 2 | 40 |
| Chef Anton's Cajun Seasoning | 2 | 22 |
| Aniseed Syrup | 2 | 10 |
| Sir Rodney's Marmalade | 3 | 81 |
| Pavlova | 3 | 17.45 |
| Teatime Chocolate Biscuits | 3 | 9.2 |

**Your job:** add the sort to the query in the box.

- Keep its three columns: `ProductName`, `CategoryId`, `UnitPrice`.
- Sort by `CategoryId`, smallest first.
- Within one category, sort by `UnitPrice`, most expensive first.
- Every product: no filter.

Practiced live on the lesson page: write the SQL right there and check it.

## 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

- [ORDER BY](https://www.ask-codey.com/glossary/order-by)
- [ASC](https://www.ask-codey.com/glossary/asc)
- [DESC](https://www.ask-codey.com/glossary/desc)
- TOP

## Cheat sheets

- [TOP and ORDER BY](https://www.ask-codey.com/cheat-sheets/select-in-t-sql-top-and-order-by-cheat-sheet)

Source: https://www.ask-codey.com/lessons/select-in-t-sql-top-and-order-by
