|
SQL> create table t( 2 sid number, 3 tid number, 4 name varchar2(20), 5 dept varchar2(30), 6 step number, 7 ttime varchar2(20) 8 ); Table created Executed in 1.391 seconds SQL> insert into t select 1,253, 'ABC ', 'MIS ',5, '10:20 ' FROM DUAL 2 UNION ALL SELECT 2,253, 'ABC ', 'MIS ',6, '10:22 ' FROM DUAL 3 UNION ALL SELECT 3,254, 'STD ', 'PE ',5, '10:22 ' FROM DUAL 4 UNION ALL SELECT 4,254, 'STD ', 'PE ',6, '10:30 ' FROM DUAL; 4 rows inserted Executed in 0.297 seconds SQL> SELECT * FROM T; SID TID NAME DEPT STEP TTIME ---------- ---------- -------------------- ------------------------------ ---------- -------------------- 1 253 ABC MIS 5 10:20 2 253 ABC MIS 6 10:22 3 254 STD PE 5 10:22 4 254 STD PE 6 10:30 Executed in 0.187 seconds SQL> SELECT SID,TID,NAME,DEPT,STEP,TTIME FROM ( 2 SELECT SID,TID,NAME,DEPT,STEP,TTIME,ROW_NUMBER()OVER(PARTITION BY TID ORDER BY TTIME DESC) RM 3 FROM T 4 ) A WHERE A.RM = 1; SID TID NAME DEPT STEP TTIME ---------- ---------- -------------------- ------------------------------ ---------- -------------------- 2 253 ABC MIS 6 10:22 4 254 STD PE 6 10:30 |
|
|
