馬克杯

select * from (select ROW_NUMBER() OVER() num , TABLENAME.* FROM TABLENAME ) AS RANKED_TABLE where num < 10
-- -------------------------------------------------- ------------------ ----------------- --------------------------- ----------------------

Example:

1. 查詢訂單檔中的前10筆資料
select * from (
select ROW_NUMBER() OVER() num , ORDERS.* FROM ORDERS
) AS RANKED_TABLE
where num <= 10

2. 查詢保單檔內保單編號900 ~ 1000的資料中， 前20筆資料
select * from (
select ROW_NUMBER() OVER() num , INSUORDER.*
FROM INSUORDER where INSUNBR between 900 and 1000 order by INSUNBR
) AS RANKED_TABLE
where num < 20

3. 查詢保單檔內保單編號900 ~ 1000的資料中， 第10 筆至第30筆資料
select * from (
select ROW_NUMBER() OVER() num , INSUORDER.*
FROM INSUORDER where INSUNBR between 900 and 1000 order by INSUNBR
) AS RANKED_TABLE
where num between 10 and 30

4. 利用 fetch 關鍵字的查詢方法，可查出保單檔內保單編號900 ~ 1000的資料中，前30筆資料，
但此語法無法查出第 xx 筆至第xx 筆資料
select * FROM INSUORDER where INSUNBR between 900 and 1000 fetch first 30 rows only