Show only the first N lines of SQL query output

Question

Is there a way to show only the first N lines of output from an SQL query? Bonus points if the request stops working after the output of lines N

I'm most interested in finding something that works in Oracle .

Answer 1

It would be useful to indicate which database you want to configure. Different databases have different syntax and methods for achieving this:

For example, in Oracle, you can do this by setting the condition to RowNum ( select ... from ... where ... rownum < 11 → will output the first 10 entries)

In MySQL you can use you can use the limit clause.

Microsoft SQL Server => SELECT TOP 10 column FROM table

PostgreSQL and MySQL => SELECT column FROM table LIMIT 10

Oracle => select * from (SELECT column FROM table ) WHERE ROWNUM <= 10 (thanks to styles)

Sybase => SET rowcount 10 SELECT column FROM table

Firebird => SELECT FIRST 10 column FROM table

NOTE. Modern ORM tools such as Hibernate provide high-level APIs (Query, Restriction, Condition) that abstract the logic of the top n lines based on the dialect you select.

Answer 2

For Oracle, the proposed and accepted solution is incorrect. Try using an order clause and the results will be unpredictable. SQL must be nested to do this in Oracle.

 select name, price from ( select name, price, row_number() over (order by price) r from items ) where r between 1 and 5; 

The above example was borrowed from http://www.adp-gmbh.ch/ora/sql/examples/first_rows.html , which discusses this topic well.

Answer 3

I know this with MySQL, but I don’t know if it is standard SQL: complete the query with "X constraint", X = n. lines you want to get.

Example:

SELECT NAME FROM EMPLOYEES ORDER BY SALARY DESC LIMIT 10;

Answer 4

For Oracle, you can try this

 select /*+ FIRST_ROWS(10) */ * from table;