By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601446 fiddles created (47986 in the last week).
create table test (
FLOW integer,
OTPERIOD char(7),
LOG_DATE date,
LOG_TIME time,
ENTITY integer
);
✓
hidden batch(es)
insert into test values( '1000', '2016.12', '2017-08-31', '17:12:18', '1059' );
insert into test values( '1000', '2016.12', '2017-08-30', '17:06:53', '1059' );
insert into test values( '1000', '2016.12', '2017-08-30', '17:05:58', '1059' );
insert into test values( '1000', '2016.12', '2017-09-17', '13:11:39', '2215' );
insert into test values( '1000', '2016.12', '2017-09-16', '13:09:57', '2215' );
insert into test values( '1000', '2016.12', '2017-09-16', '12:03:26', '2215' );
insert into test values( '1000', '2016.12', '2017-08-23', '19:17:18', '8998' );
insert into test values( '1000', '2016.12', '2017-08-22', '19:14:32', '8998' );
insert into test values( '1000', '2016.12', '2017-08-22', '17:00:08', '8998' );
✓
✓
✓
✓
✓
✓
✓
✓
✓
hidden batch(es)
select * from test;
FLOW
OTPERIOD
LOG_DATE
LOG_TIME
ENTITY
1000
2016.12
2017-08-31
17:12:18
1059
1000
2016.12
2017-08-30
17:06:53
1059
1000
2016.12
2017-08-30
17:05:58
1059
1000
2016.12
2017-09-17
13:11:39
2215
1000
2016.12
2017-09-16
13:09:57
2215
1000
2016.12
2017-09-16
12:03:26
2215
1000
2016.12
2017-08-23
19:17:18
8998
1000
2016.12
2017-08-22
19:14:32
8998
1000
2016.12
2017-08-22
17:00:08
8998
…
hidden batch(es)
-- For MySQL 8.0
select FLOW, OTPERIOD, LOG_DATE, LOG_TIME, ENTITY
from (
select
FLOW, OTPERIOD, LOG_DATE, LOG_TIME, ENTITY,
row_number() over (partition by ENTITY order by LOG_DATE desc, LOG_TIME desc) num
from test
) tt
where num = 1
;
FLOW
OTPERIOD
LOG_DATE
LOG_TIME
ENTITY
1000
2016.12
2017-08-31
17:12:18
1059
1000
2016.12
2017-09-17
13:11:39
2215
1000
2016.12
2017-08-23
19:17:18
8998
…
hidden batch(es)
-- For MySQL <=8.0
select t.*
from test t
inner join (
select max(LOG_DATE) LOG_DATE, MAX(LOG_TIME) LOG_TIME, ENTITY
from test
group by ENTITY
having concat(LOG_DATE,' ', LOG_TIME) = max(concat(LOG_DATE,' ', LOG_TIME))
) tt on tt.ENTITY = t.ENTITY and tt.LOG_DATE = t.LOG_DATE and tt.LOG_TIME = t.LOG_TIME
;
FLOW
OTPERIOD
LOG_DATE
LOG_TIME
ENTITY
1000
2016.12
2017-08-31
17:12:18
1059
1000
2016.12
2017-09-17
13:11:39
2215
1000
2016.12
2017-08-23
19:17:18
8998
…
hidden batch(es)
-- For MySQL <=8.0
select *
from test t
where concat(LOG_DATE,' ', LOG_TIME) = (
select max(concat(LOG_DATE,' ', LOG_TIME))
from test tt
where tt.ENTITY = t.ENTITY
group by ENTITY
)
;
