Limit the number of SQL query lines

Asked

Viewed 19,922 times

2

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?

4 answers

5


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

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
  • Thinking about performance and assuming this in a large data volume scenario, what is the best way to make this return?

  • 1

    Between the TOP and the ROWNUMBER, Top is considerably more efficient. Reference: TOP vs ROWNUMBER

  • The PostgreSQL does not have TOP and neither ROWNUM!

  • Edited, @Lacobus.

3

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;

2

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!

2

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
  • Instead of posting another reply, you should have edited yours and added the information

  • one answer comes to SQL and another to Postgrest

Browser other questions tagged

You are not signed in. Login or sign up in order to post.