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 states with the most relevant occurrence numbers I have in the table, I would like to narrow the query to only the first 5 lines that it returns me. How could I do that?
To limit the number of rows, you can user the LIMIT , TOP , and ROWNUM clauses. 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
As reported in the other responses, of the 3 clauses cited, PostgreSQL only supports LIMIT .
You can also use FETCH :
SELECT *
FROM bra_ocorrencias
ORDER BY num_ocorrencia DESC
FETCH 5 ROWS ONLY
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;
For this (postgresql) you can use 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 default (SQL: 2008) fetch first
select *
from bra_ocorrencias
order by num_ocorrencia desc
fetch first 5 rows only
Use LIMIT
SELECT estado, num_ocorrencia
FROM bra_ocorrencias
WHERE nome like 'João'
ORDER BY num_ocorrencia DESC
LIMIT num_limit
Read more here !