สร้าง table เพื่อต้องการ test table:
SQL> create table test as select * from all_objects;
Table created.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(30)
ข้อมูลที่อยากได้ คือ
80971 บน object_id (max อันดัยที่ 3):
SQL> select object_name ,object_id from test;
OBJECT_NAME OBJECT_ID
------------------------------ ----------
DBA_TABLESPACES 4641
DBA_TEMP_FILES 4642
.
.
.
WRH$_EVENT_HISTOGRAM_PK 80971WRH$_EVENT_HISTOGRAM_PK 80972
TEST 80990
จากนั้นเป็น step ในการหา (ใช้ rownum เข้ามาช่วย):
SQL> select * from (select object_name , object_id from test order by object_id desc) where rownum <=3 ;
OBJECT_NAME OBJECT_ID
------------------------------ ----------
TEST 80990
WRH$_EVENT_HISTOGRAM_PK 80972
WRH$_EVENT_HISTOGRAM_PK 80971
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
------------------------------ ----------
WRH$_EVENT_HISTOGRAM_PK 80971
WRH$_EVENT_HISTOGRAM_PK 80972
TEST 80990
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
------------------------------ ----------
WRH$_EVENT_HISTOGRAM_PK 80971ในที่สุดก็ได้ข้อมูลที่ต้องการ
