How would I modify the following SQL query code to return the latest entry of each employee per hour, instead of currently returning every occurrence from each employee regardless of how many times it appears in the query per each hour.

I've attached example data for the order of what currently would be in the database and the desired table output. Note: you can see it's in descending order and 2 entries (0001 and 0009) are omitted because they came from the same employees within an hour interval.

SQL Query Code:

select
     TRANSACTION_ID
     EMPLOYEE_ID
     FIRST_NAME
     LAST_NAME
     TIME_STAMP
from (select d.*,
             row_number() over (partition by EMPLOYEE_ID, trunc(TIME_STAMP, 'HH') order by TIME_STAMP desc) as SEQNUM
      from MAIN.DATABASE d
     ) d
where SEQNUM = 1;
order by TRANSACTION_ID desc;

This is the current order of the data in the database:

This is what should be returned:

However, this is what the code is currently returning as written:

Any idea what I'm missing and how I can fix it?

CodePudding user response：

The query ( in order to return what you want ) should work as this. However., I am assuming your field time_stamp is really a timestamp in the format you provided.

SQL> set lines 220
SQL> with x ( transaction_id , employee_id, first_name, last_name, time_stamp )
  2  as
  3  (
  4  select '0001' , 'AAAA', 'Adam'       , 'Akbar'     , to_timestamp('10/05/2021 04:42:42.000 PM','MM/DD/YYYY HH:MI:SS.FF3 PM') from dual union all
  5  select '0004' , 'BBBB', 'Barry'      , 'Brink'     , to_timestamp('10/06/2021 07:25:25.000 AM','MM/DD/YYYY HH:MI:SS.FF3 AM') from dual union all
  6  select '0003' , 'CCCC', 'Charlie' , 'Che'      , to_timestamp('10/06/2021 07:15:15.000 AM','MM/DD/YYYY HH:MI:SS.FF3 AM') from dual union all
  7  select '0005' , 'DDDD', 'David'      , 'Doe'           , to_timestamp('10/06/2021 07:27:27.000 AM','MM/DD/YYYY HH:MI:SS.FF3 AM') from dual union all
  8  select '0006' , 'EEEE', 'Eric'       , 'Erickson'  , to_timestamp('10/06/2021 07:29:29.000 AM','MM/DD/YYYY HH:MI:SS.FF3 AM') from dual union all
  9  select '0007' , 'FFFF', 'Fred'       , 'Foe'           , to_timestamp('10/06/2021 07:31:31.000 AM','MM/DD/YYYY HH:MI:SS.FF3 AM') from dual union all
 10  select '0008' , 'GGGG', 'George'  , 'Guy'      , to_timestamp('10/06/2021 07:33:33.000 AM','MM/DD/YYYY HH:MI:SS.FF3 AM') from dual union all
 11  select '0010' , 'HHHH', 'Henry'      , 'Hugh'          , to_timestamp('10/06/2021 07:55:55.000 AM','MM/DD/YYYY HH:MI:SS.FF3 AM') from dual union all
 12  select '0009' , 'HHHH', 'Henry'      , 'Hugh'          , to_timestamp('10/06/2021 07:54:54.000 AM','MM/DD/YYYY HH:MI:SS.FF3 AM') from dual union all
 13  select '0002' , 'AAAA', 'Adam'       , 'Akbar'         , to_timestamp('10/05/2021 04:43:43.000 PM','MM/DD/YYYY HH:MI:SS.FF3 PM') from dual
 14  )
 15  select
 16       TRANSACTION_ID ,
 17       EMPLOYEE_ID    ,
 18       FIRST_NAME     ,
 19       LAST_NAME      ,
 20       TIME_STAMP
 21  from (select x.*,
 22               row_number() over (partition by EMPLOYEE_ID, trunc(TIME_STAMP, 'HH') order by TIME_STAMP desc) as SEQNUM
 23        from x
 24       )
 25  where SEQNUM = 1
 26* order by TRANSACTION_ID desc

TRAN EMPL FIRST_N LAST_NAM TIME_STAMP
---- ---- ------- -------- ---------------------------------------------------------------------------
0010 HHHH Henry   Hugh     06-OCT-21 07.55.55.000000000 AM
0008 GGGG George  Guy      06-OCT-21 07.33.33.000000000 AM
0007 FFFF Fred    Foe      06-OCT-21 07.31.31.000000000 AM
0006 EEEE Eric    Erickson 06-OCT-21 07.29.29.000000000 AM
0005 DDDD David   Doe      06-OCT-21 07.27.27.000000000 AM
0004 BBBB Barry   Brink    06-OCT-21 07.25.25.000000000 AM
0003 CCCC Charlie Che      06-OCT-21 07.15.15.000000000 AM
0002 AAAA Adam    Akbar    05-OCT-21 04.43.43.000000000 PM

8 rows selected.

SQL>

CodePudding user response：

Your query is:

• missing commas between the terms in the SELECT clause; and
• has a ; after the WHERE filter and before the ORDER BY clause.

If you fix those issues then you get the code:

select TRANSACTION_ID,
       EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       TIME_STAMP
from   (
  select d.*,
         row_number() over (
           partition by EMPLOYEE_ID, trunc(TIME_STAMP, 'HH')
           order by TIME_STAMP desc
         ) as SEQNUM
  from   MAIN.DATABASE d
) d
where  SEQNUM = 1
order by TRANSACTION_ID desc;

Which, for the sample data:

CREATE TABLE main.database (TRANSACTION_ID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME, TIME_STAMP ) AS
SELECT '0001', 'AAAA', 'Adam',    'Akbar',    TIMESTAMP '2021-05-10 16:42:42.000' FROM DUAL UNION ALL
SELECT '0004', 'BBBB', 'Barry',   'Brink',    TIMESTAMP '2021-06-10 07:25:25.000' FROM DUAL UNION ALL
SELECT '0003', 'CCCC', 'Charlie', 'Che',      TIMESTAMP '2021-06-10 07:15:15.000' FROM DUAL UNION ALL
SELECT '0005', 'DDDD', 'David',   'Doe',      TIMESTAMP '2021-06-10 07:27:27.000' FROM DUAL UNION ALL
SELECT '0006', 'EEEE', 'Eric',    'Erickson', TIMESTAMP '2021-06-10 07:29:29.000' FROM DUAL UNION ALL
SELECT '0007', 'FFFF', 'Fred',    'Foe',      TIMESTAMP '2021-06-10 07:31:31.000' FROM DUAL UNION ALL
SELECT '0008', 'GGGG', 'George',  'Guy',      TIMESTAMP '2021-06-10 07:33:33.000' FROM DUAL UNION ALL
SELECT '0010', 'HHHH', 'Henry',   'Hugh',     TIMESTAMP '2021-06-10 07:55:55.000' FROM DUAL UNION ALL
SELECT '0009', 'HHHH', 'Henry',   'Hugh',     TIMESTAMP '2021-06-10 07:54:54.000' FROM DUAL UNION ALL
SELECT '0002', 'AAAA', 'Adam',    'Akbar',    TIMESTAMP '2021-05-10 16:43:43.000' FROM DUAL;

Works as expected:

TRANSACTION_ID	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	TIME_STAMP
0010	HHHH	Henry	Hugh	10-JUN-21 07.55.55.000000000
0008	GGGG	George	Guy	10-JUN-21 07.33.33.000000000
0007	FFFF	Fred	Foe	10-JUN-21 07.31.31.000000000
0006	EEEE	Eric	Erickson	10-JUN-21 07.29.29.000000000
0005	DDDD	David	Doe	10-JUN-21 07.27.27.000000000
0004	BBBB	Barry	Brink	10-JUN-21 07.25.25.000000000
0003	CCCC	Charlie	Che	10-JUN-21 07.15.15.000000000
0002	AAAA	Adam	Akbar	10-MAY-21 16.43.43.000000000

db<>fiddle here