CODEWITHSUNDEEP
sqlsql-servert-sql
TopBanana9000
TopBanana9000

Reputation: 898

Grouping by ID and time interval in ms sql

I am trying to write a query that groups like ids within a timespan.

Real world scenario: I want to see rows created by the same ID within 5 seconds of each other.

SELECT top 10 
Id, 
CreatedOn
FROM Logs
where ((DATEPART(SECOND, CreatedOn) + 5) - DATEPART(SECOND, CreatedOn)) < 10
GROUP BY 
DATEPART(SECOND, CreatedOn),
Id,
CreatedOn
order by CreatedOn desc

This isnt quite right but I feel like I am on the right track.

thanks in advance

Upvotes: 1

Views: 240

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You may try doing a query on the condition that the ID matches, and the seconds since epoch is within 5 seconds of the matching record:

SELECT
    t1.Id,
    t1.CreatedOn
FROM logs t1
WHERE EXISTS (SELECT 1 FROM logs t2
              WHERE t1.Id = t2.Id AND
                    t1.CreatedOn <> t2.CreatedOn AND
                    ABS(DATEDIFF(SECOND, t1.CreatedOn, t2.CreatedOn)) <= 5)
ORDER BY
    t1.CreatedOn DESC;

Upvotes: 1

san
san

Reputation: 1515

Could be further optimized this way:

SELECT t1.Id,
       ,t1.CreatedOn
FROM   logs t1
WHERE  EXISTS (
               SELECT 1 
               FROM   logs t2
               WHERE  t2.Id = t1.Id
               AND    t2.CreatedOn <> t1.CreatedOn 
               AND    ABS(DATEDIFF(SECOND, t1.CreatedOn, t2.CreatedOn)) <= 5
              )
ORDER  BY
       t1.CreatedOn DESC;

Upvotes: 0

Related Questions