SELECT TOP N 在 SQL Server、Oracle、DB2 的實作比較

比較三大資料庫取前 N 筆（含等值處理）的語法差異。

SQL Server 7

• M1（僅 N 筆）：SELECT TOP N * FROM t ORDER BY col
• M2（含等值）：SELECT TOP N WITH TIES * FROM t ORDER BY col

Oracle 8i

• M1：SELECT * FROM (SELECT * FROM t ORDER BY col DESC) WHERE ROWNUM <= N
• M2：SELECT * FROM t WHERE col >= (SELECT MIN(col) FROM (SELECT * FROM t ORDER BY col DESC) WHERE ROWNUM <= N) ORDER BY col DESC
• 錯誤寫法：WHERE ROWNUM <= N ORDER BY col（先截後排）

DB2 UDB 7

• M1：SELECT * FROM t ORDER BY col DESC FETCH FIRST N ROWS ONLY
• M2：DB2 不允許 FROM 子查詢含 ORDER BY，無直譯 M2 寫法

補充

• Access 只有 TOP N 行為等同 M2
• SQL Server 另支援 TOP N PERCENT