How can I retrieve the Top N records from a query?

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

Views: 22

Reply to This

Replies to This Discussion

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

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

For example, if you wanted to retrieve the first 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) suppliers2
WHERE rownum <= 3
ORDER BY rownum;

If you wanted to retrieve the first 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 DESC) suppliers2
WHERE rownum <= 3
ORDER BY rownum;

Reply to Discussion

RSS

Oracle Jobs in US

© 2024   Created by Maisam Agha.   Powered by

Badges  |  Report an Issue  |  Terms of Service