Limitando linhas retornadas em uma instrução SQL

Podemos limitar linhas a serem retornadas em uma instrução SQL através de duas cláusulas: WHERE e FETCH. A cláusula WHERE já foi abordada e pode ser consultada no seguinte link:

Cláusula WHERE
Cláusula FETCH

Comandos para limitar linhas retornadas

Você pode usar a cláusula de limitação de linha SQL para retornar apenas um subconjunto desses dados. A cláusula de limitação de linha SQL é nova no Oracle 12c. Ele pode ser usado após qualquer cláusula WHERE ou ORDER BY para limitar o número ou a porcentagem de linhas a serem retornadas por uma consulta. Todos os critérios WHERE quando fornecidos já se destinam a limitar os dados retornados do ponto de vista lógico – esse é todo o objetivo da cláusula WHERE.

A cláusula de limitação de linha tem uma visão muito mais ampla. Enquanto outros aspectos da sua consulta geralmente se concentram na lógica comercial, a cláusula de limitação de linhas pode ser usada para escolher, por exemplo, apenas as 10 primeiras linhas de dados consultados, a segunda dúzia de linhas ou as últimas 50 linhas.

FETCH

Os limites nas linhas retornadas por uma instrução SELECT são especificados pela instrução FETCH. Aqui está um exemplo de uma única instrução SELECT usando a cláusula de limitação de linha SQL:

SELECT * FROM ORDERS FETCH FIRST 8 ROWS ONLY;

A palavra-chave FETCH significa a presença da cláusula de limitação de linha. Neste exemplo acima, a consulta retornará até oito linhas e não mais. Por exemplo, se existir apenas seis linhas, todas as seis serão retornadas. Se existirem 500 linhas, apenas 8 serão retornadas. FETCH pode especificar um número de linhas, como acabamos de ver, ou uma porcentagem, assim:

SELECT * FROM ORDERS FETCH FIRST 50 PERCENT ROWS ONLY;

A cláusula FETCH possui as seguintes palavras chaves e opções:

A palavra reservada FETCH (obrigatória)
A palavra reservada FIRST ou NEXT, tem que usar uma ou outra é obrigatório, FIRST ou NEXT não há diferença entre eles.
Uma expressão válida que seja avaliada como número.
- Se não informar um número irá trazer a partir da linha 1.
- Uma palavra-chave opcional PERCENT se o número imediatamente anterior for interpretado como uma porcentagem em vez de um valor numérico. PERCENT nunca retornará uma linha parcial e as porcentagens são arredondadas para o número mais próximo de linhas. Por exemplo, se você especificar uma busca de 1 por cento das quatro linhas disponíveis, uma linha será retornada. Se você especificar 26% de quatro linhas disponíveis, duas linhas serão retornadas.
A palavra reservada ROW ou ROWS (não há diferença, deve usar algum)
A palavra reservada ONLY ou WITH TIES (alguma das duas deve ser usada, há diferença entre elas).
- ONLY retornará o número ou porcentagem de linhas especificadas e nada mais.
- WITH TIES é melhor compreendido com um exemplo, mas, resumindo, WITH TIES retornará tudo o que é retornado por ONLY, mas possivelmente com algumas linhas adicionais. Essas linhas adicionais serão consideradas apenas se uma cláusula ORDER BY for incluída no SELECT e esse ORDER BY classificar linhas de maneira que haja linhas restantes que sejam logicamente consistentes com a cláusula ORDER BY específica, mas omitidas arbitrariamente para satisfazer o limite de linhas.

WITH TIES

WITH TIES está vinculado a cláusula ORDER BY. Sem ORDER BY, WITH TIES se comportará da mesma forma que ONLY. Vamos considerar um exemplo de WITH TIES. Digamos que você tenha oito linhas em uma tabela ORDERS, cinco das quais têm o mesmo valor na coluna LINE_ITEMS sendo o valor de número 2 para as cinco linhas. Se você classificar as linhas por LINE_ITEMS e, em seguida, buscar apenas as primeiras 50 por cento de linhas, obterá as quatro primeiras linhas retornadas por a cláusula ORDER BY, conforme mostrado na Figura abaixo:

Executando o segundo comando da imagem acima obtemos 4 linhas, o comando WITH TIES “amarra” valores iguais, ou seja, há mais linhas com o valor 2 da coluna usada no ORDER BY que é LINE_ITEMS. O comando WITH TIES vai “olhar” os valores dessa coluna usada no ORDER BY e caso haja valores iguais o resultado não irá ser cortado, irá trazer os valores iguais até o valor ser trocado, no caso o valor é o número 2 e como vimos há cinco linhas com o valor 2. O WITH TIES não irá permitir “cortar” o resultado mas sim irá trazer as cinco linhas. Observe a imagem abaixo:

OBS: sem a cláusula ORDER BY o WITH TIES terá o mesmo resultado que ONLY.

OFFSET

Por default a consulta traz os resultados a partir da primeira linha, mas é possível “pular” algumas linhas e trazer um por exemplo da 6º linha até a 7º linha exemplo:

SELECT * FROM ORDERS OFFSET 5 ROWS FETCH FIRST 2 ROWS ONLY;

O OFFSET pulou 5 linhas iniciais do retorno da consulta e na 6º linha até a 7º linha é retornada pois o parâmetro FIRST passa (2 ROWS ONLY), ou seja vai trazer duas linhas apenas.

Considerações sobre OFFSET, se OFFSET for (omitido, valor zero ou negativo) então o retorno da consulta irá retornar normalmente, desde a primeira linha. Valor negativo será tratado como zero.

Se o número passado no OFFSET for menor que o número de linhas válidas retornadas da consulta então a consulta retornará a partir do valor passado no OFFSET, que é o caso ideal para o uso.

Se o valor passado no OFFSET for igual ou maior do que o valor de linhas retornadas então nenhuma linha será retornada pela consulta.

Caso use função agregada como count(*), sum(salary), não retornará nenhuma linha, motivo desconhecido, isso apenas com offset. Exemplo, a consulta abaixo não retorna nada:

select sum(salary)
from hr.employees
offset 10 row fetch next 2 rows only

Já informando a cláusula group by a consulta funciona e retorna uma coluna com nome SUM(SALARY) com duas linhas:

select sum(salary)
from hr.employees
group by first_name
offset 10 row fetch next 2 rows only

SUM(SALARY)
2100
2400

Exemplos Práticos

Abaixo a consulta retornará apenas uma linha, pode usar order by antes do fetch, assim irá fazer o order by e depois pegar a primeira linha e mostrar:

select * from hr.employees fetch first row only

Se usar fetch first 0 row only não irá retornar nenhum registro.

Ao executar a consulta:

select employee_id, first_name, salary from hr.employees order by salary

Terá como retorno da consulta acima a seguinte tabela abaixo:

Observe a tabela exemplo acima, vamos executar a seguinte consulta:

select employee_id, first_name, salary from hr.employees order by salary fetch next 6 rows only

Obtemos o seguinte retorno:

Usando WITH TIES na mesma consulta acima para pegar a continuação do valor repetido que no caso é o 2500 o último salário da tabela acima, veja que com a cláusula que iremos usar WITH TIES ele irá varrer os próximos resultados e se o valor do último resultado for igual as próximas linhas então irá retorná-las:

select employee_id, first_name, salary from hr.employees order by salary fetch next 6 rows with ties

Veja que o comando FETCH diz pra trazer 6 linhas mas trouxe 8 como pode ver na tabela acima 8 linhas no retorno da consulta, isso ocorreu pois na sexta linha tinha o valor 2500 e por conta parâmetro WITH TIES passado ele “buscou” esse valor “2500” nos próximos registros subsequentes e todos com o valor igual é retornado, por isso retornou 8 linhas, a nona linha é outro valor e não 2500, essa é a importância do ORDER BY, o ORDER BY está dizendo pra ordenar por salary e é na coluna salary que o WITH TIES irá “olhar” para trazer as linhas com valores idênticos.