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.
Key Features
Posts
