TOP N QUERIES in Oracle 12c

Share via:

Dear Readers,

In this article, we will see the TOP N QUERIES in Oracle 12c

12c Introduced the Top-N queries concept to fetch required rows by writing  the simple queries. Top-N queries provide a method for limiting the result to a specific number of rows.

A Top-N query is used to retrieve the top or bottom N rows from an ordered set. It is a simple way to fetch the required rows and then closing the statement.

For selecting TOP N Rows Oracle providing some following keywords.

OFFSET  ROW / ROWS

FETCH FIRST / NEXT  ROWCOUNT

FETCH FIRST / NEXT  ROWCOUNT ONLY / WITH TIES

FETCH FIRST / NEXT PERCENT(%)  PERCENT ROWS

 

TOM>> select * from emp;

 

 

TOM >>select * from emp order by sal desc;

 

 

TOM>>select * from emp order by sal desc fetch first 5 rows only;

TOM>>select * from emp order by sal desc fetch first 3 rows only;

TOM>>select * from emp order by sal desc fetch first 2 rows only;

TOM>>select * from emp order by sal desc fetch first 2 rows with ties;

TOM>>select * from emp order by sal desc offset 3 rows fetch next 2 rows only;

TOM>>select * from emp order by sal desc offset 2 rows fetch first 5 rows only;

TOM>>select * from emp order by sal desc offset 10 rows fetch first 3 rows only;

TOM>>select  * from emp order by sal desc fetch first 25 percent rows only;

 

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform

 

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (27 votes, average: 5.00 out of 5)
Loading...

5 thoughts on “TOP N QUERIES in Oracle 12c

Add Comment