DISTINCT, TOP and ORDER BY: reduce and sort a result

A query can return all rows that meet a condition and still not answer exactly what we are looking for. Maybe we need to know the existing statuses without repeating them, review only the most recent orders or present prices from low to high.

The idea

These three constructs affect the result in different ways: DISTINCT eliminates repetitions, TOP limits the number of rows, and ORDER BY sets their order.

ORDER BY: declare order

Tables do not have a reading order that should be assumed. Even if a query appears to return rows in the same sequence, that behavior can change with the plan or available indexes. An execution plan is the strategy SQL Server chooses to obtain the result; an index is a structure that organizes values to support particular access paths.

ORDER BY Expresion1 ASC, Expresion2 DESC

ASC sorts from lowest to highest and DESC from highest to lowest. We can combine several expressions to break ties.

SELECT
    p.IdProducto,
    p.Nombre,
    p.Precio
FROM dbo.Producto AS p
ORDER BY
    p.Precio DESC,
    p.IdProducto ASC;

DESC sorts the price from highest to lowest. When two products have the same price, IdProducto ASC provides a second stable criterion.

TOP: limit the number of rows

TOP limits how many rows the query returns. The number follows SELECT; by itself, it does not determine which rows should come first.

SELECT TOP (Cantidad)
    Columna1,
    Columna2
FROM dbo.Tabla;

To get the three highest priced products:

SELECT TOP (3)
    p.IdProducto,
    p.Nombre,
    p.Precio
FROM dbo.Producto AS p
ORDER BY
    p.Precio DESC,
    p.IdProducto ASC;

TOP without ORDER BY limits rows, but does not define which rows are first from a business point of view. If we ask for “the three most expensive”, the order is part of the question.

It can also be expressed as a percentage:

SELECT TOP (10) PERCENT
    p.IdProducto,
    p.Nombre,
    p.Precio
FROM dbo.Producto AS p
ORDER BY p.Precio DESC;

In small results, rounding can produce a quantity that does not seem intuitive. For processes that require an exact number it is usually clearer to use a number of rows.

DISTINCT: remove repeated rows

DISTINCT compares all selected expressions and keeps one row for each distinct combination.

SELECT DISTINCT
    Columna1,
    Columna2
FROM dbo.Tabla;

If we only want to know the statuses that appear in the orders:

SELECT DISTINCT
    p.Estado
FROM dbo.Pedido AS p
ORDER BY p.Estado;

DISTINCT considers all selected expressions together. If we add IdPedido, each identifier and state combination will be different, even if the state is repeated:

SELECT DISTINCT
    p.IdPedido,
    p.Estado
FROM dbo.Pedido AS p;

In that case, DISTINCT does not reduce the rows because IdPedido already identifies each order.

What should be checked

DISTINCT can hide a poorly constructed join. If duplicates appear after adding a JOIN, first review the relationship cardinality—how many rows on one side can match a row on the other—and the condition written in ON.

For paginated results, a stable order requires a column, or combination of columns, to break ties. Sorting only by date can produce inconsistent pages when multiple rows share the same value.

TOP limits the complete result. When a certain number of rows is needed inside each group, use window functions: calculations that observe related rows without collapsing them into one row. ROW_NUMBER, RANK, and DENSE_RANK perform that work through OVER and are explained in their own reference.

Related to

• WHERE: Limit the rows before sorting the result.
• OFFSET and FETCH: allow pagination.
• ROW_NUMBER, RANK and DENSE_RANK: classify within a window.
• INNER JOIN: You can multiply rows when multiple matches exist.