Oracle has some sentences for top n
version oracle 10g 11g
TOP-N
SELECT * FROM (
SELECT name, id
FROM test
ORDER BY id DESC
) WHERE rownum <= 5;
SELECT * FROM (
SELECT name, id
FROM test
WHERE type='0'
ORDER BY id DESC
) WHERE rownum <= 5;
分页
SELECT * FROM (
SELECT * FROM (
SELECT name, id,
rownum AS rn
FROM test
WHERE type='0'
ORDER BY id DESC
) WHERE rownum <= 20
) WHERE rn > 10;
Top-N with joins
SELECT * FROM (
SELECT t.name , t.id, e.dept
FROM test t, em_ct e
WHERE t.id = e.id
AND t.type='0'
ORDER BY t.idDESC
) WHERE rownum <= 5
/