CODEWITHSUNDEEP
sqloracle-databasecountdistinct
Ong Xiang
Ong Xiang

Reputation: 7

Oracle: How to get distinct ID based on date column and time column, with count column added at back

I have table like this:

diskno     regdate     regtime     remarks
d001       2020/07/01  12:59:00    abc
d001       2020/07/01  13:00:06    def
d002       2020/07/02  16:00:00    ghi
d003       2020/07/04  07:00:00    jkl
d003       2020/07/05  06:00:50    mno

And would like to get only 1 latest record of each diskno and add count column for that diskno at the back. How should I do this in Oracle SQL?

Expected result:

diskno     regdate     regtime     remarks    diskcount
d001       2020/07/01  13:00:06    def        2
d002       2020/07/02  16:00:00    ghi        1
d003       2020/07/05  06:00:50    mno        2

*Both regdate and regtime are varchar2.

Upvotes: 0

Views: 192

Answers (3)

MT0
MT0

Reputation: 168071

You can use GROUP BY and aggregation functions with the KEEP ( DENSE_RANK ... ) syntax to get rows that a maximum value dependent on another maximum value:

SELECT diskno,
       MAX( regdate ) as regdate,
       MAX( regtime ) KEEP ( DENSE_RANK LAST ORDER BY regdate, regtime )
         AS regtime,
       MAX( remarks ) KEEP ( DENSE_RANK LAST ORDER BY regdate, regtime )
         AS remarks,
       COUNT(*) AS diskcount
FROM   table_name
GROUP BY diskno

For your sample data:

CREATE TABLE table_name ( diskno, regdate, regtime, remarks ) AS
SELECT 'd001', '2020/07/01', '12:59:00', 'abc' FROM DUAL UNION ALL
SELECT 'd001', '2020/07/01', '13:00:06', 'def' FROM DUAL UNION ALL
SELECT 'd002', '2020/07/02', '16:00:00', 'ghi' FROM DUAL UNION ALL
SELECT 'd003', '2020/07/04', '07:00:00', 'jkl' FROM DUAL UNION ALL
SELECT 'd003', '2020/07/05', '06:00:50', 'mno' FROM DUAL;

This outputs:

DISKNO | REGDATE    | REGTIME  | REMARKS | DISKCOUNT
:----- | :--------- | :------- | :------ | --------:
d001   | 2020/07/01 | 13:00:06 | def     |         2
d002   | 2020/07/02 | 16:00:00 | ghi     |         1
d003   | 2020/07/05 | 06:00:50 | mno     |         2

db<>fiddle here

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65323

One option uses analytic functions such as ROW_NUMBER() and COUNT() OVER ...

WITH t2 AS
( 
 SELECT t.*,
        ROW_NUMBER() OVER 
       ( PARTITION BY diskno ORDER BY regdate DESC, regtime DESC ) AS rn,
        COUNT(*) OVER ( PARTITION BY diskno ) AS diskcount
   FROM t 
)  
 SELECT diskno, regdate, regtime, remarks, diskcount
   FROM t2
  WHERE rn = 1

where PARTITION BY stands for grouping by diskno, and the data ORDERed descendingly BY date and time in order to determine the latest record.

Demo

P.S : Prefer storing your data within a DATE or TIMESTAMP type of columns where date and time portions are combined, rather than a CHAR type column. Why do we prefer more specialized style to less, and cumbersome one.

Upvotes: 1

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

Taking in consideration what strange field is the hour, I would try this

 with t as (
 select 'd001' as diskno, '2020/07/01' as regdate,   '12:59:00' as regtime,     'abc' as remarks from dual union all
 select 'd001' as diskno, '2020/07/01' as regdate,   '13:00:06' as regtime,     'def' as remarks from dual union all
 select 'd002' as diskno, '2020/07/02' as regdate,   '16:00:00' as regtime,     'ghi' as remarks from dual union all
 select 'd003' as diskno, '2020/07/04' as regdate,   '07:00:00' as regtime,     'jkl' as remarks from dual union all
 select 'd003' as diskno, '2020/07/05' as regdate,   '06:00:50' as regtime,     'mno' as remarks from dual )
 select diskno, max(regdate) ,
                case when diskno = 'd003' then min(regtime) else max(regtime) end,
                max(remarks), count(diskno) as sum_diskno
 from t group by diskno order by 1
;

Example

SQL> with t as (
  2   select 'd001' as diskno, '2020/07/01' as regdate,   '12:59:00' as regtime,     'abc' as remarks from dual union all
  3   select 'd001' as diskno, '2020/07/01' as regdate,   '13:00:06' as regtime,     'def' as remarks from dual union all
  4   select 'd002' as diskno, '2020/07/02' as regdate,   '16:00:00' as regtime,     'ghi' as remarks from dual union all
  5   select 'd003' as diskno, '2020/07/04' as regdate,   '07:00:00' as regtime,     'jkl' as remarks from dual union all
  6   select 'd003' as diskno, '2020/07/05' as regdate,   '06:00:50' as regtime,     'mno' as remarks from dual )
  7   select diskno, max(regdate) ,
  8                  case when diskno = 'd003' then min(regtime) else max(regtime) end,
  9                  max(remarks), count(diskno) as sum_diskno
 10*  from t group by diskno order by 1
SQL> /

DISK MAX(REGDAT CASEWHEN MAX SUM_DISKNO
---- ---------- -------- --- ----------
d001 2020/07/01 13:00:06 def          2
d002 2020/07/02 16:00:00 ghi          1
d003 2020/07/05 06:00:50 mno          2

SQL>

Upvotes: 1

Related Questions