Selecting latest time stamps when multiple exist within the same minute

Question

I am in need of gathering only the last entry within the minute that the objects wrote to the tables.

SELECT 
    A.event_time_local AS Datetime, 'PackagingLine' as TagName, 
    A.state_cd AS VALUE
FROM 
    util_log A, util_state B
WHERE 
    ent_id = 12 
    AND event_time_local BETWEEN '2017-07-18 07:00:00' AND '2017-07-18 15:00:00' 
    AND B.state_cd IN (0, 1, 3, 4)
    AND A.state_cd = b.state_cd

Data returned

2017-07-18 08:08:35.000 EM45_PackagingLine  0
2017-07-18 08:08:49.000 EM45_PackagingLine  1
2017-07-18 09:31:30.000 EM45_PackagingLine  0
2017-07-18 09:31:38.000 EM45_PackagingLine  0
2017-07-18 09:31:50.000 EM45_PackagingLine  1

Data wanted

2017-07-18 08:08:49.000 EM45_PackagingLine  1
2017-07-18 09:31:50.000 EM45_PackagingLine  1

Any help at all would be awesome.

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**25 years** ago) and its use is discouraged — marc_s, Jul 18 '17 at 17:11
Possible duplicate of [Retrieving the last record in each group](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Tab Alleman, Jul 18 '17 at 18:24

score 0 · Answer 1 · answered Jul 18 '17 at 18:26

This will add a row_number to your result set that first groups event_time_local in hour-long buckets and numbers them latest to earliest. Then it selects only those rows that were latest in their bucket.

I'm not entirely clear how big you wanted your 'bucket' to be -- it looks like an hour in your sample data but I'm not sure. Just tweak the Date logic to provide the properly sized interval if that isn't quite what you wanted.

WITH NumberedResultSet AS 
(
    SELECT 
        A.event_time_local AS Datetime, 'PackagingLine' as TagName, 
        A.state_cd AS VALUE
        , ROW_NUMBER() OVER (Partition By  DATEADD(hour, DATEDIFF(hour, 0, event_time_local), 0) Order By event_time_local desc) as rn
    FROM 
        util_log A, util_state B
    WHERE 
        ent_id = 12 
        AND event_time_local BETWEEN '2017-07-18 07:00:00' AND '2017-07-18 15:00:00' 
        AND B.state_cd IN (0, 1, 3, 4)
        AND A.state_cd = b.state_cd
)

SELECT
    *
FROM
    NumberedResultSet
WHERE 
    rn = 1

Selecting latest time stamps when multiple exist within the same minute

1 Answers1