I've been Googling for how you would implement a Top-N query with different databases and here's what I could find on a discussion thread at StackOverflow:
IBM DB2:
SELECT * FROM emp FETCH FIRST 10 ROWS ONLY
Informix, InterBase/Firebird:
SELECT FIRST 10 * FROM emp
MS SQL:
SELECT TOP 10 * FROM emp
MySQL, PostgreSQL, SQLite:
SELECT * FROM emp LIMIT 10
Oracle:
SELECT * FROM emp WHERE ROWNUM <= 10
Unfortunately, ANSI-SQL doesn't provide any way to do this :-(
1 comment:
FYI, according to another article I read, with Oracle the ROWNUM is applied before any ORDER BY clause if you specify one; in this case you must use a nested select:
SELECT *
FROM
(SELECT *
FROM emp
ORDER BY NVL(SALARY, 0) DESC)
WHERE ROWNUM < 10;
Post a Comment