ADVERTISEMENT

Oracle/PLSQL: Retrieve Bottom N records from a query

---

Question:  How can I retrieve the Bottom N records from a query?

For example, what if I wanted to retrieve the last 3 records from my query results. How can I do this?

Answer:  To retrieve the Bottom N records from a query, you can use the following syntax:

SELECT *
FROM (your query ordered in reverse) alias_name
WHERE rownum <= Rows_to_return
ORDER BY rownum DESC;

If you want to retrieve the bottom records from a query, you need to order your results in reverse in the "your query ordered in reverse" section of the solution above.

For example, if you wanted to retrieve the last 3 records from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_name DESC) suppliers2
WHERE rownum <= 3
ORDER BY rownum DESC;

Notice that although you want the last 3 records sorted by supplier_name in ascending order, you actually sort the supplier_name in descending order in this solution.

If you wanted to retrieve the last 3 records from the suppliers table, sorted by supplier_name in descending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_name) suppliers2
WHERE rownum <= 3
ORDER BY rownum DESC;

If you wanted to retrieve the last 5 records from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_id DESC) suppliers2
WHERE rownum <= 5
ORDER BY rownum DESC;

If you wanted to retrieve the last 5 records from the suppliers table, sorted by supplier_id in descending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_id) suppliers2
WHERE rownum <= 5
ORDER BY rownum DESC;