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 :-(
at

1 comment:

  1. Blue LightningSeptember 29, 2009 at 1:08 PM

    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;

    ReplyDelete