Retrieve Top N records from a query - OracleConnections2024-03-29T05:52:37Zhttp://www.oracleconnections.com/forum/topics/retrieve-top-n-records-from-a-query?feed=yes&xn_auth=noTo retrieve the Top N records…tag:www.oracleconnections.com,2013-04-26:6612035:Comment:17262013-04-26T10:10:35.397ZEleanor Deehttp://www.oracleconnections.com/profile/EleanorDee
<p>To retrieve the Top N records from a query, you can use the following syntax:</p>
<p>SELECT *<br></br>FROM (your ordered query) alias_name<br></br>WHERE rownum <= Rows_to_return<br></br>ORDER BY rownum;</p>
<p>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:</p>
<p>SELECT *<br></br>FROM (select * from suppliers ORDER BY supplier_name) suppliers2<br></br>WHERE rownum <= 3<br></br>ORDER BY…</p>
<p>To retrieve the Top N records from a query, you can use the following syntax:</p>
<p>SELECT *<br/>FROM (your ordered query) alias_name<br/>WHERE rownum <= Rows_to_return<br/>ORDER BY rownum;</p>
<p>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:</p>
<p>SELECT *<br/>FROM (select * from suppliers ORDER BY supplier_name) suppliers2<br/>WHERE rownum <= 3<br/>ORDER BY rownum;</p>
<p>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:</p>
<p>SELECT *<br/>FROM (select * from suppliers ORDER BY supplier_name DESC) suppliers2<br/>WHERE rownum <= 3<br/>ORDER BY rownum;</p>