nettoon493 nettoon493 -4 years ago 35
SQL Question

How use Group by and Max(date) multi record

i want Group by by Max(Datetime) each record. but i query have dupplicatate record. i want don't duplicate record.

SQL:

select a.pmn_code,
a.ref_period,
a.SERVICE_TYPE,
min(a.status) keep (dense_rank last order by a.updated_dtm) as status,
max(a.updated_dtm) as updated_dtm
from tempChkStatus a
group by a.pmn_code, a.ref_period, a.SERVICE_TYPE


Data Table tempChkStatus:

PMN_CODE | REF_PERIOD | SERVICE_TYPE | STATUS | UPDATED_DTM
A | 01/2016 | OI | I | 19/08/2016 10:54:44
A | 01/2016 | OP | N | 06/06/2017 15:09:55
A | 02/2016 | OT | I | 31/08/2016 08:37:45
A | 02/2016 | OT | N | 12/10/2016 11:13:56
A | 04/2016 | OI | I | 19/08/2016 10:54:44
A | 04/2016 | OP | N | 06/06/2017 15:09:55


Result SQL:

PMN_CODE | REF_PERIOD | SERVICE_TYPE | STATUS | UPDATED_DTM
A | 01/2016 | OI | I | 19/08/2016 10:54:44
A | 01/2016 | OP | N | 06/06/2017 15:09:55
A | 02/2016 | OT | N | 12/10/2016 11:13:56
A | 04/2016 | OI | I | 19/08/2016 10:54:44
A | 04/2016 | OP | N | 06/06/2017 15:09:55


But I want Result:

PMN_CODE | REF_PERIOD | SERVICE_TYPE | STATUS | UPDATED_DTM
A | 01/2016 | OP | N | 06/06/2017 15:09:55
A | 02/2016 | OT | N | 12/10/2016 11:13:56
A | 04/2016 | OP | N | 06/06/2017 15:09:55


Help me please. Thanks advance ;)

Answer Source
with tempChkStatus (

PMN_CODE, REF_PERIOD  , SERVICE_TYPE    , STATUS , UPDATED_DTM) as
(
select 'A',         '01/2016'      ,'OI',              'I',      to_date('19/08/2016 10:54:44', 'dd/mm/yyyy hh24:mi:ss') from dual union all
select 'A',         '01/2016'      ,'OP',              'N',      to_date('06/06/2017 15:09:55', 'dd/mm/yyyy hh24:mi:ss') from dual union all
select 'A',         '02/2016'      ,'OT',              'I',      to_date('31/08/2016 08:37:45', 'dd/mm/yyyy hh24:mi:ss') from dual union all
select 'A',         '02/2016'      ,'OT',              'N',      to_date('12/10/2016 11:13:56', 'dd/mm/yyyy hh24:mi:ss') from dual union all
select 'A',         '04/2016'      ,'OI',              'I',      to_date('19/08/2016 10:54:44', 'dd/mm/yyyy hh24:mi:ss') from dual union all
select 'A',         '04/2016'      ,'OP',              'N',      to_date('06/06/2017 15:09:55', 'dd/mm/yyyy hh24:mi:ss') from dual 
)
select * from (
select e.*, max(updated_dtm) over (partition by ref_period) md from tempchkstatus e 
)
where updated_dtm = md
;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download