LuisLlamas Logo
ordenar-resultados-order-by-sql

Sorting SQL Results with ORDER BY

  • 5 min
  • Beginner

So far, we have learned to select columns and filter rows. Now we are going to learn how to sort the results we get.

If you’ve already been experimenting with the previous articles, something you’ve probably noticed is that the results sometimes come out “ordered as they please”.

Here is a fundamental theoretical concept to understand. Tables in a relational database represent mathematical sets, and sets have NO order.

Even though you often see data sorted by the primary key by default, you should never rely on that default order.

If the database engine decides to use a different index, or parallelize the query, the order will change.

The only way to guarantee that data is returned in a specific order is by explicitly using the ORDER BY clause.

Basic Syntax

The ORDER BY clause is placed at the end of our SELECT statement.

The basic syntax of ORDER BY is as follows:

SELECT columns
FROM table
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
Copied!
  • columns: The columns you want to select in the query.
  • table: The table from which the data is being selected.
  • column1, column2, …: The columns by which you want to sort the results.
  • ASC|DESC: Specifies whether the order is ascending (ASC, default) or descending (DESC).

For example

-- Sort customers by last name alphabetically (A-Z)
SELECT Nombre, Apellido
FROM Clientes
WHERE Ciudad = 'Madrid'
ORDER BY Apellido ASC;

-- Sort products from most expensive to cheapest
SELECT Nombre, Precio
FROM Productos
ORDER BY Precio DESC;
Copied!

Sorting by Expressions and Functions

In addition to sorting by columns, we can also sort results using expressions or functions. For example, if we want to sort employees by the length of their name, we can do it as follows:

SELECT Nombre, Apellido, Salario
FROM Empleados
ORDER BY LEN(Nombre) ASC;
Copied!

In this example, LEN(Nombre) returns the length of each employee’s name, and the results are sorted based on this value.

Suppose we want to sort employees by their hire year. We can use the YEAR function to extract the year from the FechaContratacion column:

SELECT Nombre, Apellido, FechaContratacion
FROM Empleados
ORDER BY YEAR(FechaContratacion) ASC;
Copied!

In this case, employees will be sorted by the year they were hired, from oldest to most recent.

Sorting by Multiple Columns

In the real world, sorting by a single column is rarely enough. Imagine you sort your users by Apellido (Last Name).

If you have 50 people with the last name “García”, in what order do “the Garcías” appear?

Again, random (among themselves).

To solve this, we can specify multiple columns separated by commas. SQL Server will sort by the first one; if there are ties, it will use the second one to break the tie, and so on.

SELECT Apellido, Nombre, FechaRegistro
FROM Usuarios
ORDER BY Apellido ASC, Nombre ASC;
Copied!

In this example:

  1. First, it groups all the “García”, then “Gómez”, etc.
  2. Within the “García” group, it sorts by Nombre (Ana, Benito, Carlos…).

You can mix directions. You might want to sort by Departamento (Department) ascending, but within each department, show employees with Salario (Salary) descending (highest paid first).

ORDER BY Departamento ASC, Salario DESC

Execution Order and ALIASES

Remember that in WHERE we couldn’t use aliases defined in SELECT? Well, with ORDER BY we can.

The logical order of execution is:

FROM

WHERE

SELECT (Aliases are created here)

ORDER BY

Since ORDER BY is executed after SELECT, the engine already knows the calculated columns and their names.

SELECT
    Nombre,
    Precio * 1.21 AS PrecioConIVA
FROM Productos
ORDER BY PrecioConIVA DESC; -- This works perfectly!
Copied!

What about NULLs?

If we sort a column that has NULL values, where are they placed? At the beginning or at the end?

In SQL Server, NULL values are considered the lowest possible values.

  • If you sort ASC: NULLs will appear first.
  • If you sort DESC: NULLs will appear last.

If you need different logic (for example, you want to sort ascending but have NULLs appear at the end), you will have to use tricks with functions like ISNULL or CASE expressions, which we will see later.

Sorting by Position

You might see something like this in old code:

SELECT Nombre, Apellido, Email
FROM Clientes
ORDER BY 2, 1;
Copied!

This means: “Sort by column 2 (Apellido) and then by column 1 (Nombre)”

Although T-SQL allows it, don’t do that. It’s bad code. Always use the column name.

If tomorrow someone changes the SELECT and adds a column at the beginning, your ORDER BY 2 will start sorting by a different column without you noticing, breaking the application’s logic.

Performance: The Cost of Sorting

Sorting is one of the most expensive operations for a database. It requires CPU and memory to compare and reorganize data.

If you sort millions of records without a suitable index, you will see your query get stuck.

  • If the column you are sorting by has an Index, SQL Server can return the data instantly because it is already physically sorted or in the index tree.
  • If it does not have an index, SQL Server will have to perform a Sort operation in memory which is slow.

Therefore, as a general rule, sort only if it is strictly necessary for the final presentation of the data.

On this page