Limit the number of SQL query lines

Question

I have the following Query

SELECT estado, num_ocorrencia
FROM bra_ocorrencias
WHERE nome like 'João'
ORDER BY num_ocorrencia DESC

It returns me all the states with the most relevant occurrences numbers I have in the table, I would like to reduce the query only the 5 first lines that she returns to me. How could I do this?

Answer 1

To limit the number of lines, you can user the clauses LIMIT,TOP and ROWNUM. Ex:

SELECT estado, num_ocorrencia
FROM bra_ocorrencias
WHERE nome like 'João'
LIMIT 5
ORDER BY num_ocorrencia DESC

Or

SELECT estado, num_ocorrencia
TOP 5
FROM bra_ocorrencias
WHERE nome like 'João'
ORDER BY num_ocorrencia DESC;

Or

SELECT estado, num_ocorrencia
FROM bra_ocorrencias
WHERE nome like 'João'
AND ROWNUM <= 5
ORDER BY num_ocorrencia DESC

Reference

• SQL LIMIT, TOP AND ROWNUM

Edited

As informed in the other responses, of the 3 clauses cited, the PostgreSQL supports only the LIMIT.

There is also the possibility to use the FETCH:

SELECT *
FROM  bra_ocorrencias
ORDER BY num_ocorrencia  DESC
FETCH 5 ROWS ONLY

Answer 2

The TOP clause of the SQL language is used to limit the number of records returned by a query

SELECT TOP 5  num_ocorrencia, estado
FROM bra_ocorrencias
WHERE nome like 'João'
ORDER BY num_ocorrencia DESC;

Answer 3

Use the LIMIT

SELECT estado, num_ocorrencia
FROM bra_ocorrencias
WHERE nome like 'João'
ORDER BY num_ocorrencia DESC
LIMIT num_limit

Read more here!

Answer 4

For this (postgresql) you can use the limit

select *
from bra_ocorrencias
order by num_ocorrencia desc
limit 5

If performance is important look for an index in the score. you can also use the standard ( SQL: 2008 )fetch first

select *
from bra_ocorrencias
order by num_ocorrencia  desc
fetch first 5 rows only