Is there a way to only show the first N lines of output from a 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.
+3
A:
I know it with MySQL but I don't know if it's standard SQL : end you Query with 'limit X', X = n. of lines you want to get.
Example :
SELECT NAME FROM EMPLOYEES ORDER BY SALARY DESC LIMIT 10;
Olivier Pons
2009-05-17 07:30:38
+1. I suspect LIMIT is standard SQL. In any case, it works with PostgreSQL too.
j_random_hacker
2009-05-17 07:32:29
Limit is not standard. MSSQL doesn't have it.
Spencer Ruport
2009-05-17 07:52:53
LIMIT is not standard, though it is fairly widespread. The SQL:2008 standard for some reason went with the DB2 method of FETCH FIRST, though very few other products implement that.... See http://troels.arvin.dk/db/rdbms/#select-limit for more details.
kquinn
2009-05-17 10:25:48
+18
A:
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.
Rutesh Makhijani
2009-05-17 07:34:37
Ingres and informix (SQL 92 I believe)SELECT FIRST 10 column FROM table
corlettk
2009-05-17 07:43:57
Yup... SELECT FIRST 10 is the standard, apparently. Ref: http://www.hamslab.com/~man2/sql/SQL98/index.html#sql92
corlettk
2009-05-17 07:45:08
Sorry, but the Oracle solution does not work. Rownum needs nested SQL to work, otherwise it will not handle any form of ordering other than the trivial example with no order clause.
stili
2009-05-17 10:13:39
+2
A:
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.
stili
2009-05-17 10:11:23
When you need the first N according to a particular order, consider an index on the orderby column (e.g. "price" in this example) - the optimiser might find it cheaper to read the index in order of price, thus giving the first N rows quicker without scanning the whole table.Without the index, the db must read all rows from the table before it can definitively say it's found the top N rows.
Jeffrey Kemp
2009-05-18 05:49:55