標準 SQL:2008 FETCH FIRST

現代資料庫（PostgreSQL、Oracle 12c+、SQL Server 2012+、DB2、MySQL 8.0.13+）支援標準語法：

SELECT * FROM tbl ORDER BY id FETCH FIRST 10 ROWS ONLY;

各資料庫傳統寫法對照

資料庫	語法	範例
MySQL / MariaDB / SQLite / PostgreSQL	LIMIT n [OFFSET m]	SELECT * FROM tab LIMIT 10 SELECT * FROM tab LIMIT 10 OFFSET 20 (第 3 頁)
Oracle 11g 及之前	ROWNUM 偽欄	SELECT * FROM (SELECT * FROM tab ORDER BY id) WHERE ROWNUM <= 10
Oracle 12c+	FETCH FIRST / ROWNUM	SELECT * FROM tab ORDER BY id FETCH FIRST 10 ROWS ONLY
SQL Server 2005-2008	TOP n	SELECT TOP 10 * FROM tab ORDER BY id
SQL Server 2012+	OFFSET ... FETCH	SELECT * FROM tab ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
DB2	FETCH FIRST n ROWS ONLY	SELECT * FROM tab FETCH FIRST 10 ROWS ONLY
Firebird	FIRST n SKIP m	SELECT FIRST 10 SKIP 20 * FROM tab

分組取前 N（Top-N per Group）

需求：每個 PO_NO 取最新 2 筆交易。

MySQL 8.0+ / PostgreSQL / Oracle 12c+ / SQL Server 2012+：窗口函數（最佳）

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY PO_NO ORDER BY Trans_Date DESC) AS rn
    FROM tmp_Trans
) t WHERE rn <= 2;

MySQL 5.7 / 老版本：變數模擬

SELECT PO_NO, Trans_Date, Amount FROM (
    SELECT 
        PO_NO, Trans_Date, Amount,
        @rn := IF(@prev = PO_NO, @rn + 1, 1) AS rn,
        @prev := PO_NO
    FROM tmp_Trans, (SELECT @rn := 0, @prev := NULL) vars
    ORDER BY PO_NO, Trans_Date DESC
) t WHERE rn <= 2;

SQL Server 2005+：CROSS APPLY

SELECT DISTINCT b.*
FROM tmp_Trans a
CROSS APPLY (
    SELECT TOP 2 * FROM tmp_Trans
    WHERE PO_NO = a.PO_NO
    ORDER BY Trans_Date DESC
) b;

Oracle ROWNUM 陷阱

-- 錯誤：先 ROWNUM 過濾再排序，結果非預期
SELECT * FROM tab WHERE ROWNUM <= 10 ORDER BY name DESC;

-- 正確：子查詢先排序
SELECT * FROM (SELECT * FROM tab ORDER BY name DESC) WHERE ROWNUM <= 10;

分頁效能建議

• 深分頁（OFFSET 1000000）效能極差，改用 游標分頁（WHERE id > last_id ORDER BY id LIMIT 10）
• 配合覆蓋索引避免回表
• 大表禁止 OFFSET，必須用游標/Keyset 分頁