ADVERTISEMENT

Oracle/PLSQL: 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;