วันจันทร์ที่ 17 มีนาคม พ.ศ. 2551

ต้องการ query เอาข้อมูล max อันดับที่ 3 เท่านั้น

สร้าง 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 80971
WRH$_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

ในที่สุดก็ได้ข้อมูลที่ต้องการ

1 ความคิดเห็น:

Surachart กล่าวว่า...

Change to use row_number ^^

Old Step:
SQL> create table test as select * from all_objects;

Table created.

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


Change to (New Step) ->

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