Oracle : 12c TOP N QUERIES

Oracle 12c TOP N QUERIES

 

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;

 

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 “Oracle : 12c TOP N QUERIES

Add Comment