ORDER BY e LIMIT: ordem e ranking

Colocando o resultado em ordem

Até agora o arquivo respondia de forma espalhada. Ele devolvia as linhas na ordem que fosse mais cômoda para buscar, juntar, filtrar e entregar.

Mas quase sempre uma pessoa precisa de mais do que um punhado de linhas: precisa de ordem.

Na «Kotomarket» isso aparece na hora:

• os itens mais caros;
• os itens mais baratos;
• os pedidos mais recentes;
• os itens com o maior estoque;
• os usuários em ordem alfabética;
• os últimos eventos do registro.

Para isso o SQL tem o ORDER BY.

SELECT name, price
FROM products
ORDER BY price;

Essa consulta ordena os itens por preço.

Por padrão, a ordenação é crescente:

ORDER BY price

é a mesma coisa que:

ORDER BY price ASC

ASC vem de ascending — crescente.

Se você precisa da ordem inversa, use o DESC:

SELECT name, price
FROM products
ORDER BY price DESC;

DESC vem de descending — decrescente.

Para números é simples:

ASC  → 1, 2, 3, 4, 5
DESC → 5, 4, 3, 2, 1

Para textos, a ordenação segue o alfabeto, conforme as regras do banco e do locale em uso.

SELECT name, category
FROM products
ORDER BY name ASC;

Essa consulta mostra os itens por nome, do começo ao fim do alfabeto.

QUERY: O arquivo pode buscar as linhas como bem entender. Mas um relatório só começa onde você mesmo define a ordem.

Sem ORDER BY não existe ordem

Uma regra muito importante:

O SQL não garante a ordem das linhas sem ORDER BY.

Por exemplo:

SELECT id, name, price
FROM products;

Uma consulta dessas pode devolver as linhas numa ordem hoje e em outra amanhã.

Às vezes parece que o banco devolve as linhas:

• na ordem em que foram inseridas;
• por id;
• do jeito que elas estão guardadas na tabela;
• do jeito que elas aparecem na interface.

Mas não dá para contar com isso.

O banco pode escolher outro plano de execução, usar outro índice, ler os dados de outro jeito depois de atualizar as estatísticas ou depois que a tabela mudar. E a ordem do resultado muda, mesmo que a consulta SQL continue exatamente a mesma.

Se a ordem importa, ela precisa estar escrita de forma explícita:

SELECT id, name, price
FROM products
ORDER BY id;

ou:

SELECT id, name, price
FROM products
ORDER BY price DESC;

A regra é simples:

Sem ORDER BY, nenhuma ordem foi prometida.

Isso é especialmente importante antes do LIMIT. Porque o LIMIT pega "as primeiras linhas", e sem ordenação não dá para saber quais linhas o banco vai considerar as primeiras.

ASC e DESC

O ORDER BY tem duas direções de ordenação.

ASC — crescente:

SELECT name, price
FROM products
ORDER BY price ASC;

Para o preço, isso significa:

primeiro os baratos, depois os caros.

DESC — decrescente:

SELECT name, price
FROM products
ORDER BY price DESC;

Para o preço, isso significa:

primeiro os caros, depois os baratos.

Se a direção não for indicada, vale o ASC.

Ou seja, estas duas consultas são equivalentes:

SELECT name, price
FROM products
ORDER BY price;

SELECT name, price
FROM products
ORDER BY price ASC;

Para quem está começando, ajuda ler a consulta em voz alta:

ORDER BY price ASC

— ordene por preço, do menor para o maior.

ORDER BY price DESC

— ordene por preço, do maior para o menor.

LIMIT: pegar só as primeiras linhas

O LIMIT limita a quantidade de linhas do resultado.

Por exemplo:

SELECT name, price
FROM products
LIMIT 3;

Essa consulta devolve apenas três linhas.

Mas, sozinho, o LIMIT não diz quais linhas você quer. Ele simplesmente pega as primeiras linhas do resultado que o banco produziu.

Por isso, para uma seleção com sentido, o LIMIT quase sempre anda junto com o ORDER BY.

Os três itens mais baratos:

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 3;

Os três itens mais caros:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Os pedidos mais recentes:

SELECT id, user_id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

A lógica é sempre a mesma:

1. O ORDER BY coloca as linhas na ordem desejada.
2. O LIMIT pega o topo dessa ordem.

ORDER BY price DESC
LIMIT 5

se lê assim:

ponha os itens mais caros no topo e pegue os cinco primeiros.

Por que o ORDER BY fica quase no fim da consulta

Em SQL, a ordem em que você escreve as partes da consulta não é exatamente a ordem em que é mais fácil pensar na execução dela.

Por exemplo:

SELECT name, price
FROM products
WHERE category = 'Книги'
ORDER BY price DESC
LIMIT 5;

Dá para ler essa consulta em passos:

1. FROM products — pegue a tabela de itens.
2. WHERE category = 'Книги' — deixe só os livros.
3. SELECT name, price — escolha as colunas que interessam.
4. ORDER BY price DESC — ordene o resultado por preço.
5. LIMIT 5 — fique com as cinco primeiras linhas.

Ou seja, a ordenação não é aplicada à tabela inteira, e sim ao resultado depois da filtragem.

Se a tabela tem mil itens, mas apenas vinte livros, a consulta primeiro separa os livros, depois ordena esses livros por preço e só então pega os cinco mais caros.

Isso ajuda a ler as consultas do jeito certo:

WHERE

define quais linhas entram.

ORDER BY

define a ordem em que elas serão mostradas.

LIMIT

define quantas linhas ficam no topo.

Ordenando por várias colunas

Às vezes uma coluna só não basta.

Por exemplo, você precisa ordenar os itens:

1. primeiro por categoria;
2. dentro de cada categoria — por preço, dos caros para os baratos.

Para isso, liste várias expressões no ORDER BY, separadas por vírgula:

SELECT name, category, price
FROM products
ORDER BY category ASC, price DESC;

Essa consulta se lê assim:

primeiro ordene por categoria em ordem crescente e, quando a categoria for a mesma, ordene por preço em ordem decrescente dentro dela.

Exemplo de resultado:

A primeira chave de ordenação é a principal:

category ASC

Ela junta as linhas por categoria.

A segunda chave só entra em cena onde a primeira é igual:

price DESC

Ela organiza os itens dentro de uma mesma categoria.

Dá para definir uma direção própria para cada coluna:

ORDER BY category ASC, price DESC, name ASC

Isso significa:

1. categoria em ordem crescente;
2. dentro da categoria, preço em ordem decrescente;
3. se o preço for igual, nome em ordem crescente.

Ordenando por uma expressão

Dá para ordenar não só por uma coluna pronta, mas também por um cálculo.

Por exemplo, na tabela products existem:

• price — o preço do item;
• stock — a quantidade em estoque.

Se você quer descobrir quais itens prendem mais dinheiro parado, dá para calcular o valor em estoque:

price * stock

A consulta:

SELECT name, price, stock
FROM products
ORDER BY price * stock DESC;

Ela não ordena os itens só pelo preço nem só pelo estoque, e sim pelo preço multiplicado pela quantidade.

Ou seja, um item de 1000 com 100 em estoque pode ficar acima de um item de 5000 com 2 em estoque.

Porque:

1000 * 100 = 100000
5000 * 2   = 10000

O ORDER BY sabe trabalhar com expressões assim:

ORDER BY price * stock DESC

Isso é prático quando a ordem depende de um cálculo, e não de uma única coluna.

Ordenando por um apelido

Se a expressão é longa, fica cansativo repeti-la no ORDER BY.

Você pode batizar a expressão com um apelido:

SELECT
    name,
    price,
    stock,
    price * stock AS stock_value
FROM products
ORDER BY stock_value DESC;

Aqui:

price * stock AS stock_value

cria no resultado uma coluna calculada chamada stock_value.

E depois:

ORDER BY stock_value DESC

ordena por esse valor calculado.

Isso funciona porque o ORDER BY é aplicado, logicamente, depois que a lista do SELECT foi montada. Na hora da ordenação, o apelido stock_value já existe.

Mas é importante não confundir com o WHERE.

Assim não dá:

SELECT
    name,
    price,
    stock,
    price * stock AS stock_value
FROM products
WHERE stock_value > 10000;

No WHERE, o apelido do SELECT ainda não está disponível.

O certo é repetir a expressão:

SELECT
    name,
    price,
    stock,
    price * stock AS stock_value
FROM products
WHERE price * stock > 10000
ORDER BY stock_value DESC;

Ou usar uma subconsulta — mas isso já é assunto dos próximos módulos.

A ideia principal desta aula:

No ORDER BY dá para usar um apelido do SELECT. No WHERE, não.

Para onde vão os NULL na ordenação

NULL significa ausência de valor. Na hora de ordenar, é preciso decidir onde essas linhas vão parar:

• no começo;
• no fim.

Por padrão, na ordenação o PostgreSQL considera o NULL maior que qualquer valor comum.

Por isso, numa ordenação crescente:

ORDER BY city ASC

as linhas com NULL ficam no fim.

E numa ordenação decrescente:

ORDER BY city DESC

as linhas com NULL ficam no começo.

Para não depender dos padrões, dá para escrever isso de forma explícita:

ORDER BY city ASC NULLS FIRST

Isso significa:

ordene as cidades em ordem crescente, mas ponha no começo as linhas sem cidade.

Ou:

ORDER BY city ASC NULLS LAST

Isso significa:

ordene as cidades em ordem crescente e ponha no fim as linhas sem cidade.

Dá para usar com DESC também:

ORDER BY price DESC NULLS LAST

Isso é prático para rankings.

Por exemplo, se alguns itens estão com o preço desconhecido, esta consulta:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

no PostgreSQL pode levar os itens com NULL para o topo, porque com DESC os NULL vêm primeiro.

Para obter justamente os itens mais caros com preço conhecido, é melhor escrever:

SELECT name, price
FROM products
ORDER BY price DESC NULLS LAST
LIMIT 5;

Assim os itens de preço desconhecido não atrapalham o ranking.

Importante para a portabilidade: em SGBDs diferentes, a posição padrão dos NULL pode variar. No PostgreSQL dá para usar explicitamente NULLS FIRST e NULLS LAST. No MySQL essa sintaxe não é usada, e o comportamento padrão é outro: o NULL costuma ser considerado menor que os valores comuns.

Empates: quando os valores são iguais

Imagine que estamos escolhendo os três itens mais caros:

SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Se todos os itens têm preços diferentes, a ordem é clara.

Mas e se vários itens custarem o mesmo?

Por exemplo:

Os três primeiros itens têm o mesmo preço.

A cláusula:

ORDER BY price DESC

garante uma coisa só:

os itens de 3000 vão ficar acima dos itens de 1000.

Mas ela não garante em que ordem os itens de 3000 vão ficar entre si.

À primeira vista, isso pode parecer um detalhe. Mas, para relatórios, testes e páginas de resultados, é importante.

Se várias linhas têm o mesmo valor na coluna de ordenação, a ordem entre elas não é definida. Ela pode mudar depois de uma atualização dos dados, de uma troca do plano de execução ou até entre duas execuções parecidas.

Para que a ordem fique totalmente previsível, acrescenta-se uma segunda chave de ordenação — normalmente o id único:

SELECT id, name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 3;

Agora a ordenação se lê assim:

1. primeiro por preço, dos caros para os baratos;
2. se o preço for igual — por id, do menor para o maior.

Essa ordem já é estável.

Uma ordem estável para o LIMIT e as páginas

Uma ordem não determinística é especialmente perigosa junto com o LIMIT.

Por exemplo:

SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 10;

Se muitos itens têm o mesmo preço, o banco pode escolher quaisquer dez linhas do grupo de valores iguais que fica na linha de corte do ranking.

Hoje o item entra no top 10.
Amanhã, com os mesmos preços, pode não entrar.

Isso não é um defeito do banco. É que você não descreveu a ordem por completo.

Para um resultado estável, acrescente uma coluna única no fim da ordenação:

SELECT id, name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 10;

Isso é especialmente importante para a paginação.

Por exemplo, a primeira página:

ORDER BY price DESC
LIMIT 10

e a segunda página, com deslocamento:

ORDER BY price DESC
LIMIT 10 OFFSET 10

Se as linhas têm o mesmo preço e não existe uma ordenação adicional por id, uma mesma linha pode "pular" de uma página para a outra.

Mais confiável:

ORDER BY price DESC, id ASC
LIMIT 10 OFFSET 10

A regra principal:

Se você usa LIMIT para um relatório, um ranking ou uma página, a ordenação precisa determinar por completo a ordem das linhas.

Dá para ordenar por uma coluna que não está no SELECT?

Muitas vezes você não quer mostrar colunas técnicas no relatório, mas precisa delas para ordenar o resultado.

Por exemplo, você quer mostrar os nomes dos itens, mas ordená-los por preço:

SELECT name
FROM products
ORDER BY price DESC;

Isso é permitido.

O price não precisa estar na lista do SELECT para ser usado no ORDER BY.

A consulta devolve só o name, mas a ordem das linhas é definida pelo preço.

Isso é prático quando a coluna serve à lógica da saída, mas não interessa a quem lê o resultado.

Por exemplo:

SELECT name
FROM products
ORDER BY created_at DESC
LIMIT 10;

Assim dá para pegar os nomes dos dez itens mais novos sem mostrar a data de criação.

Combinações típicas de ORDER BY e LIMIT

A dupla ORDER BY + LIMIT aparece o tempo todo em SQL.

Os itens mais caros:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

Os itens mais baratos:

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 5;

Os pedidos mais recentes:

SELECT id, user_id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

Os primeiros usuários em ordem alfabética:

SELECT id, name
FROM users
ORDER BY name ASC
LIMIT 20;

Os itens com o maior estoque:

SELECT name, stock
FROM products
ORDER BY stock DESC
LIMIT 10;

Os itens com o maior valor em estoque:

SELECT
    name,
    price,
    stock,
    price * stock AS stock_value
FROM products
ORDER BY stock_value DESC
LIMIT 10;

Em todos os casos, o esquema é o mesmo:

ORDER BY o_que_consideramos_importante
LIMIT quantas_linhas_precisamos

QUERY: Primeiro diga ao arquivo o que conta como importante. Só depois peça o topo.