Thursday, December 29, 2016

Retrieve Middle N records from a query



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

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...