Thursday, April 2, 2009

Top-N query

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:

Blue Lightning said...

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;