How to find maximum number of third?

I hope to hear your idea from this question. I learned to find maximum number of third by using ROWNUM.

I learned to use ROW_NUMBER function as well.

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

Create Table:

SQL> create table test as select * from all_objects;

Table created.

1. Use ROWNUM

SQL> select object_name ,object_id from test;
.
.
.
V_1 56532
V_2 56533
V_3 56534
TEST 56535

SQL> select * from (select object_name, object_id from test order by object_id desc) where rownum <=3 ;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
TEST 56535
V_3 56534
V_2 56533

SQL> select * from (select object_name, object_id from test order by object_id desc) where rownum <=3 order by object_id;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
V_2 56533
V_3 56534
TEST 56535

SQL> select * from (select * from (select object_name, object_id from test order by object_id desc) where rownum <=3 order by object_id) where rownum <=1;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
V_2 56533

- Check Execution Plan
----------------------------------------------------------
Plan hash value: 627665718
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | | 852 (2)| 00:00:11 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 3 | 90 | | 852 (2)| 00:00:11 |
|* 3 | SORT ORDER BY STOPKEY | | 3 | 90 | 1648K| 852 (2)| 00:00:11 |
|* 4 | COUNT STOPKEY | | | | | | |
| 5 | VIEW | | 41621 | 1219K| | 507 (2)| 00:00:07 |
|* 6 | SORT ORDER BY STOPKEY| | 41621 | 1219K| 1648K| 507 (2)| 00:00:07 |
| 7 | TABLE ACCESS FULL | TEST | 41621 | 1219K| | 163 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------

2. use ROW_NUMBER function

SQL> select object_name, object_id from (select object_id, object_name, row_number () over (order by object_id desc) id from test) x where id=3;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
V_2 56533

- Check Execution Plan
----------------------------------------------------------
Plan hash value: 1795822849
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41621 | 1747K| | 507 (2)| 00:00:07 |
|* 1 | VIEW | | 41621 | 1747K| | 507 (2)| 00:00:07 |
|* 2 | WINDOW SORT PUSHED RANK| | 41621 | 1219K| 1648K| 507 (2)| 00:00:07 |
| 3 | TABLE ACCESS FULL | TEST | 41621 | 1219K| | 163 (2)| 00:00:02 |
-----------------------------------------------------------------------------------------

These show the same result, but spent the different Cost.