Show only the first N lines of output of a SQL query

Question

Is there a way to only show the first N lines of output from an SQL query?
Bonus points, if the query stops running once the N lines are outputted.

I am most interested in finding something which works in Oracle.

Answer 1

It would be helpful if you specify what database you are targetting. Different databases have different syntax and techniques to achieve this:

For example in Oracle you can ahieve this by putting condition on RowNum (select ... from ... where ... rownum < 11 -> would result in outputting first 10 records)

In MySQL you can use you can use 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 stili)

Sybase => SET rowcount 10 SELECT column FROM table

Firebird => SELECT FIRST 10 column FROM table

NOTE: Modern ORM tools such as Hibernate give high level API (Query, Restriction, Condition interfaces) that abstract the logic of top n rows based on the dialect you choose.

Answer 2

For Oracle the suggested and accepted solution is wrong. Try using an order clause, and the results will be unpredictable. The SQL will need to be nested to accomplish 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 example above was borrowed from http://www.adp-gmbh.ch/ora/sql/examples/first_rows.html which has a good discussion on this topic.