Retrieve Middle N records from a query
Question: How can I retrieve the Middle N records from a query?
For example, what if I wanted to retrieve records 3 to 5 from my query results. How can I do this?
Answer: To retrieve the Middle N records from a query, you can use the following syntax:
SELECT * FROM (select alias_name.*, rownum rnum from (-- your ordered query --) alias_name where rownum <= UPPER_BOUND ) WHERE rnum >= LOWER_BOUND;
For example, if you wanted to retrieve records 3 through 5 from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:
SELECT * FROM (select suppliers2.*, rownum rnum from (select * from suppliers ORDER BY supplier_name) suppliers2 where rownum <= 5 ) WHERE rnum >= 3;
If you wanted to retrieve records 3 through 5 from the suppliers table, sorted by supplier_name in descending order, you would run the following query:
SELECT * FROM (select suppliers2.*, rownum rnum from (select * from suppliers ORDER BY supplier_name DESC) suppliers2 where rownum <= 5 ) WHERE rnum >= 3;
If you wanted to retrieve records 2 through 4 from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:
SELECT * FROM (select suppliers2.*, rownum rnum from (select * from suppliers ORDER BY supplier_id) suppliers2 where rownum <= 4 ) WHERE rnum >= 2;
If you wanted to retrieve records 2 through 4 from the suppliers table, sorted by supplier_id in descending order, you would run the following query:
SELECT * FROM (select suppliers2.*, rownum rnum from (select * from suppliers ORDER BY supplier_id DESC) suppliers2 where rownum <= 4 ) WHERE rnum >= 2;
No comments:
Post a Comment