Friday, January 11, 2013

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;