0



I want to extract hourly reports of a database for each process, but it has entries every second.

Since this is too much data to work with, I would like to get only the latest update of each hour. I am not an expert in SQL and tried already some "simple" solutions which did not work for me.

I hope you can help me out here.

SELECT
    report_id, start_date_local, process,
    productivity, dw_last_updated
    
FROM table_name

WHERE start_date_local > DATEADD(year,-1,GETDATE())
    
ORDER BY dw_last_updated, ppr_report_id desc
report_id start_date_local process productivity dw_last_updated
0001 2021-01-14 00:00:00.0 x 0.0551 2021-02-16 06:16:19.0
0002 2021-01-14 00:00:00.0 y 0.0333 2021-02-16 06:16:19.0
0003 2021-01-14 00:00:00.0 z 0.0164 2021-02-16 06:16:19.0
0004 2021-01-14 00:00:00.0 x 0.0850 2021-02-16 06:23:21.0
0005 2021-01-14 00:00:00.0 y 0.0238 2021-02-16 06:17:27.0
0006 2021-01-14 00:00:00.0 z 0.0542 2021-02-16 06:44:10.0

So in this example I would like to get only report_id = 0004, 0005, 0006

Dino
  • 3
  • 4
  • Please only tag the RDBMS you are *really* using. – Thom A Jan 13 '22 at 11:52
  • I don't use redshift but if you have window function support you need something like this [Get top 1 row per group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group/14346780) – Stu Jan 13 '22 at 12:06

1 Answers1

0

What you want is to group entries by process and get the max timestamp. So you need to put your query in GROUP BY clause:

manual: https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

SELECT process,
       report_id,
       start_date_local,

       productivity,
       max(dw_last_updated) last_updated

FROM table_name

WHERE start_date_local > DATEADD(year, -1, GETDATE())

GROUP BY process
Solitone
  • 26
  • 2
  • I think "max(dw_last_updated)" is giving me the the latest timestamp based on seconds. I've got the isssue that I need the hourly reports only. Seconds are not relevant for me. But since the column is timestamp without zone, I can not even change the format or convert it without the seconds. So there is no workaround as far I can see. – Dino Jan 18 '22 at 08:39