SQL TOP, FETCH FIRST e WITH TIES

Lição 2.9 · Tempo de leitura: ~9 min · Tarefas para praticar ↓

SQL TOP e FETCH FIRST n ROWS ONLY são as alternativas ao LIMIT: TOP é usado pelo SQL Server, e FETCH FIRST é o padrão SQL suportado por PostgreSQL, Oracle, MariaDB e outros. Nesta lição, você vai aprender a sintaxe padrão, a paginação com OFFSET ... FETCH, a opção WITH TIES, que mantém as linhas com valores iguais, PERCENT e as formas antigas do Oracle e do Firebird.

Na lição 2.7, você limitou resultados com LIMIT e OFFSET. Essa sintaxe funciona no MySQL, MariaDB, PostgreSQL e SQLite, mas o SQL Server e o Oracle não a aceitam. Quando você muda de banco de dados, lê consultas de outras pessoas ou se prepara para uma entrevista, encontra várias outras formas de escrever a mesma ideia.

Por Que Limitar Linhas Tem Várias Sintaxes

Por muito tempo, limitar linhas não fazia parte do padrão SQL, e cada banco de dados inventou a própria sintaxe:

• MySQL e PostgreSQL adicionaram LIMIT, e o SQLite o copiou.
• O SQL Server adicionou TOP.
• O Oracle tinha a pseudocoluna ROWNUM.
• O Firebird adicionou FIRST e SKIP.

A cláusula padrão FETCH FIRST só apareceu no SQL:2008, quando esses hábitos já estavam consolidados. Hoje a maioria dos bancos de dados suporta a forma padrão além da própria, mas o MySQL e o SQLite ainda não.

FETCH FIRST n ROWS ONLY: a Sintaxe Padrão

FETCH FIRST vem depois de ORDER BY, onde ficaria o LIMIT:

SELECT title, length
FROM film
ORDER BY length
FETCH FIRST 3 ROWS ONLY;

Resultado: três dos filmes mais curtos, de 46 minutos. A consulta funciona no MariaDB 10.6+, PostgreSQL e Oracle; o MySQL dá erro de sintaxe.

As palavras FIRST e NEXT, ROW e ROWS são intercambiáveis: FETCH NEXT 1 ROW ONLY significa o mesmo que FETCH FIRST 1 ROWS ONLY. Escolha o que se lê melhor. Sem o número, FETCH FIRST ROW ONLY devolve uma linha.

Paginação com OFFSET ... FETCH NEXT

A forma padrão de OFFSET é escrita com a palavra ROWS e vem antes de FETCH:

SELECT title, length
FROM film
ORDER BY length, title
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

Resultado: os filmes 11 a 15 por duração, o mesmo que LIMIT 5 OFFSET 10.

Essa é a única forma de paginar no SQL Server, e lá ela exige ORDER BY: OFFSET sem ordenação é erro de sintaxe.

WITH TIES: Manter as Linhas que Compartilham o Último Valor

Veja de novo os filmes mais curtos. Cinco filmes têm 46 minutos, mas FETCH FIRST 3 ROWS ONLY devolve só três deles, e quais três é o banco de dados que decide.

WITH TIES resolve isso: devolve as primeiras N linhas mais todas as linhas seguintes que têm os mesmos valores de ordenação da linha N.

SELECT title, length
FROM film
ORDER BY length
FETCH FIRST 3 ROWS WITH TIES;

Resultado: 5 linhas. Os cinco filmes de 46 minutos são devolvidos, porque o terceiro compartilha a duração com outros dois.

WITH TIES responde com honestidade a perguntas como "os três filmes mais curtos": nenhum filme fica de fora só porque compartilha um valor com o último.

Regras do WITH TIES

• Exige ORDER BY: os empates são definidos pelas colunas de ordenação. Sem ordenação, PostgreSQL e MariaDB dão erro.
• Uma coluna de desempate elimina os empates. Com ORDER BY length, title não há duas linhas iguais, e WITH TIES devolve exatamente 3 linhas.
• O resultado pode ser bem maior que N. Os filmes mais longos do Sakila têm 185 minutos, e ORDER BY length DESC FETCH FIRST 3 ROWS WITH TIES devolve 10 filmes.

A mesma consulta no PostgreSQL, no banco Bookings, encontra as rotas mais longas:

SELECT flight_no, departure_city, arrival_city, duration
FROM routes
ORDER BY duration DESC
FETCH FIRST 3 ROWS WITH TIES;

Resultado: 4 rotas de 8 horas e 50 minutos: voos entre Moscou e Yuzhno-Sakhalinsk ou Petropavlovsk.

WITH TIES em FETCH é suportado pelo PostgreSQL 13+, MariaDB 10.6+ e Oracle 12c+.

SQL Server: TOP

O SQL Server coloca TOP logo depois de SELECT:

SELECT TOP (3) Name, ListPrice
FROM Product
ORDER BY ListPrice DESC;

Resultado: três dos produtos mais caros do AdventureWorks.

Os parênteses são opcionais para um número (TOP 3 também funciona), mas recomendados: com eles, TOP aceita também uma variável ou expressão, como TOP (@n).

TOP tem o próprio WITH TIES, e lá ele só funciona com TOP: no SQL Server, OFFSET ... FETCH não suporta WITH TIES.

SELECT TOP (3) WITH TIES Name, ListPrice
FROM Product
ORDER BY ListPrice DESC;

Resultado: 5 produtos. Cinco tamanhos da bicicleta Road-150 Red custam 3578.27.

Armadilhas do TOP

• TOP sem ORDER BY é permitido, mas devolve linhas arbitrárias, assim como LIMIT sem ordenação.
• TOP ... WITH TIES sem ORDER BY é erro.
• O SQL Server não aceita FETCH FIRST sozinho: FETCH só é permitido depois de OFFSET. Para as primeiras linhas, escreva OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY ou use TOP.

Devolver uma Porcentagem de Linhas

Às vezes você não precisa de 10 linhas, e sim de 10% delas. SQL Server e Oracle fazem isso diretamente:

SELECT TOP (1) PERCENT Name, ListPrice
FROM Product
ORDER BY ListPrice DESC;

Resultado: 3 linhas. 1% de 295 produtos é 2,95, e o SQL Server arredonda para cima.

No Oracle, usa-se a forma padrão:

SELECT first_name, last_name, salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 5 PERCENT ROWS ONLY;

Resultado: os 6 funcionários com os maiores salários: 5% de 107 é 5,35, arredondado para cima.

O DuckDB escreve a porcentagem no LIMIT: LIMIT 10%. PostgreSQL, MariaDB e MySQL não suportam porcentagens: neles você conta as linhas primeiro e calcula o número por conta própria.

Oracle: ROWNUM e FETCH FIRST

Antes da versão 12c, o Oracle não tinha FETCH FIRST, e o código antigo limita linhas com a pseudocoluna ROWNUM. Ela numera as linhas à medida que são lidas, antes de ORDER BY ser aplicado. Por isso esta consulta está errada:

SELECT first_name, last_name, salary
FROM hr.employees
WHERE ROWNUM <= 3
ORDER BY salary;

Resultado: não os três menores salários, e sim as três primeiras linhas lidas (17000, 17000 e 24000), ordenadas depois.

Para ordenar primeiro, o código antigo coloca a consulta ordenada em uma subconsulta e aplica ROWNUM ao resultado dela:

SELECT first_name, last_name, salary
FROM (
    SELECT first_name, last_name, salary
    FROM hr.employees
    ORDER BY salary
)
WHERE ROWNUM <= 3;

Resultado: os três menores salários: 2100, 2200 e 2200.

Outra armadilha: WHERE ROWNUM > 1 não devolve nenhuma linha. A primeira linha recebe o número 1, não passa na condição e é descartada; a seguinte vira a número 1 de novo, e assim por diante.

No Oracle 12c e posteriores, use simplesmente FETCH FIRST, inclusive com WITH TIES:

SELECT first_name, last_name, salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 7 ROWS WITH TIES;

Resultado: 8 funcionários: o 7º e o 8º ganham 12008.

O Oracle continua sem aceitar LIMIT, mesmo na versão 26ai.

Firebird: FIRST, SKIP e ROWS

O Firebird tem duas sintaxes próprias. FIRST e SKIP vêm logo depois de SELECT, como TOP:

SELECT FIRST 3 SKIP 2 first_name, last_name, salary
FROM employee
ORDER BY salary DESC;

Resultado: os funcionários 3 a 5 por salário: duas linhas são puladas e três são devolvidas.

ROWS m TO n vem no final da consulta e recebe números de linha em vez de uma quantidade:

SELECT first_name, last_name, salary
FROM employee
ORDER BY salary DESC
ROWS 3 TO 5;

Resultado: os mesmos funcionários 3 a 5. ROWS 3 sozinho significa as três primeiras linhas.

Desde o Firebird 3.0, o padrão OFFSET ... FETCH também funciona. WITH TIES não é suportado, nem no Firebird 5.

WITH TIES no MySQL e no SQLite

MySQL e SQLite não têm FETCH nem WITH TIES, mas o mesmo resultado pode ser montado com ferramentas que você vai aprender mais adiante no curso. Aqui vai uma prévia.

Uma subconsulta encontra o valor da terceira linha, e a consulta externa pega todos os filmes que não são mais longos:

SELECT title, length
FROM film
WHERE length <= (
    SELECT length
    FROM film
    ORDER BY length
    LIMIT 1 OFFSET 2
)
ORDER BY length, title;

Resultado: os mesmos 5 filmes de 46 minutos de FETCH FIRST 3 ROWS WITH TIES. Subconsultas são tratadas na lição 6.2.

A função de janela RANK() dá o mesmo posto a valores iguais, então rnk <= 3 mantém todos os empates:

SELECT title, length
FROM (
    SELECT title, length, RANK() OVER (ORDER BY length) AS rnk
    FROM film
) AS ranked
WHERE rnk <= 3
ORDER BY length, title;

Resultado: os mesmos 5 filmes. RANK() é tratado na lição 7.2.

Sintaxe para Limitar Linhas por Banco de Dados

Se uma consulta precisa funcionar em vários bancos de dados, o padrão OFFSET ... FETCH com ORDER BY cobre a maioria: todos, exceto MySQL e SQLite. Com esta lição você conclui o básico da recuperação de linhas; o próximo módulo mostra como transformar valores com funções SQL.

Principais conclusões:

• FETCH FIRST n ROWS ONLY é a alternativa padrão ao LIMIT; MySQL e SQLite não a suportam.
• OFFSET m ROWS FETCH NEXT n ROWS ONLY pagina os resultados; no SQL Server, exige ORDER BY.
• WITH TIES acrescenta as linhas que compartilham os valores de ordenação da última linha, então o resultado pode passar de N.
• O SQL Server usa TOP (n), TOP (n) WITH TIES e TOP (n) PERCENT.
• O ROWNUM do Oracle é atribuído antes do ORDER BY: ordene primeiro em uma subconsulta ou use FETCH FIRST no 12c+.
• No MySQL e no SQLite, monte o WITH TIES com uma subconsulta ou com RANK().

Perguntas Frequentes

Qual é o equivalente do LIMIT no SQL Server?

Para as primeiras linhas, use SELECT TOP (n) ...; para paginar, ORDER BY ... OFFSET m ROWS FETCH NEXT n ROWS ONLY. O SQL Server não aceita LIMIT nem permite FETCH sem OFFSET.

O que WITH TIES faz no SQL?

Devolve as primeiras N linhas mais cada linha seguinte com os mesmos valores nas colunas do ORDER BY que a linha N. FETCH FIRST 3 ROWS WITH TIES pode devolver 3, 5 ou 10 linhas, dependendo de quantas compartilham o terceiro valor.

O MySQL suporta FETCH FIRST ou WITH TIES?

Não. MySQL 8 e 9 suportam apenas LIMIT. O MariaDB suporta FETCH FIRST ... WITH TIES desde a versão 10.6. No MySQL, obtenha o mesmo resultado com uma subconsulta ou com RANK().

Como limitar linhas no Oracle?

No Oracle 12c e posteriores, use FETCH FIRST n ROWS ONLY depois de ORDER BY. Em versões antigas, ordene em uma subconsulta e filtre o resultado com WHERE ROWNUM <= n. O Oracle não suporta LIMIT.

Qual é a diferença entre FETCH FIRST e FETCH NEXT?

Nenhuma: FIRST e NEXT são sinônimos, assim como ROW e ROWS. Por convenção, FETCH FIRST é usado sem OFFSET e FETCH NEXT depois dele.