排序後再 SELECT TOP N 筆資料

By Casey | July 13, 2022 | 1 minutes

寫法一：

1
2
3
4
5
6
7
SELECT *
FROM (
    SELECT *
    FROM TABLE_A
    ORDER BY TABLE_A.COLUMN_A DESC
)
WHERE ROWNUM <= 5;

寫法二：

1
2
3
4
SELECT *
FROM TABLE_A
ORDER BY TABLE_A.COLUMN_A DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

寫法三： (Ask Tom) 速度最快

1
2
3
4
5
6
7
SELECT * FROM (
    SELECT a.*, ROWNUM rnum FROM (
        SELECT *
        FROM TABLE_A
        ORDER BY TABLE_A.COLUMN_A DESC
  ) a WHERE ROWNUM <= MAX_ROW
) WHERE rnum >= MIN_ROW

寫法四：

1
2
3
4
5
6
SELECT *
FROM (
    SELECT *
    FROM TABLE_A
    ORDER BY TABLE_A.COLUMN_A DESC
) WHERE ROWNUM BETWEEN MIN_ROW AND MAX_ROW