Hello
I'm confused below is a example where analityc function max() over (partition by order by ) return another rows if ordering is DESC nor ordeing is ASC?! I do not know why!?
The main idea is to find the LAST - I have not used the LAST function because of performance issue, and I have found this issue.
Why in column LAST_ID_DESC select did not found the LAST record?
SELECT:
| select |
| max(ID) over(partition by t.e_id, t.f_id order by timestamp desc) last_id_desc, |
| max(ID) over(partition by t.e_id, t.f_id order by timestamp asc) last_id_asc, |
| t.* |
| from test_table t; |
| |
TABLE:
CREATE TABLE TEST_TABLE
(
ID NUMBER,
TIMESTAMP TIMESTAMP(6),
F_ID NUMBER,
E_ID VARCHAR2(100 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Insert into TEST_TABLE
(ID, TIMESTAMP, F_ID, E_ID)
Values
(1, TO_TIMESTAMP('2015/06/03 11:21:44.454107','YYYY/MM/DD HH24:MI:SS.FF'), 536, '005002');
Insert into TEST_TABLE
(ID, TIMESTAMP, F_ID, E_ID)
Values
(2, TO_TIMESTAMP('2015/06/03 11:21:44.454107','YYYY/MM/DD HH24:MI:SS.FF'), 538, '005002');
Insert into TEST_TABLE
(ID, TIMESTAMP, F_ID, E_ID)
Values
(3, TO_TIMESTAMP('2015/06/03 11:21:43.986106','YYYY/MM/DD HH24:MI:SS.FF'), 540, '005002');
Insert into TEST_TABLE
(ID, TIMESTAMP, F_ID, E_ID)
Values
(4, TO_TIMESTAMP('2015/06/03 11:21:43.986106','YYYY/MM/DD HH24:MI:SS.FF'), 542, '005002');
Insert into TEST_TABLE
(ID, TIMESTAMP, F_ID, E_ID)
Values
(5, TO_TIMESTAMP('2015/06/03 11:21:43.455705','YYYY/MM/DD HH24:MI:SS.FF'), 544, '005002');
Insert into TEST_TABLE
(ID, TIMESTAMP, F_ID, E_ID)
Values
(6, TO_TIMESTAMP('2015/06/03 11:21:43.315305','YYYY/MM/DD HH24:MI:SS.FF'), 544, '005002');
Insert into TEST_TABLE
(ID, TIMESTAMP, F_ID, E_ID)
Values
(7, TO_TIMESTAMP('2015/06/03 11:21:43.455705','YYYY/MM/DD HH24:MI:SS.FF'), 546, '005002');
Insert into TEST_TABLE
(ID, TIMESTAMP, F_ID, E_ID)
Values
(8, TO_TIMESTAMP('2015/06/03 11:21:43.315305','YYYY/MM/DD HH24:MI:SS.FF'), 546, '005002');
COMMIT;