Search by best values for every minute-CodePudding

The problem I'm having is that I have a table of data that has a new row added every second (imagine the structure {id, timestamp(datetime), value}). I would like to do a single query for MSSQL to go through the table and output only the number of objects that have the top 2 values asc for each minute ( also output ).

Any ideas please?

Sample data:

1   2015-01-01 00:00:00  128
2   2015-01-01 00:00:01  128
3   2015-01-01 00:00:04  129
4   2015-01-01 00:00:05  123
...
67  2015-01-01 00:00:59  128


Output : 

starttime  endtime  number
2015-01-01 00:00:00  2015-01-01 00:00:59  4

CodePudding user response：

As mentioned by @ZoharPeled in the comments, you can use DATEDIFF and DATEADD to partition by minute. Then use DENSE_RANK to take the top two value results.

SELECT
  t.StartTime,
  EndTime = DATEADD(second, 59, t.StartTime),
  number = COUNT(*)
FROM (
    SELECT *,
      rn = DENSE_RANK() OVER (PARTITION BY v.StartTime ORDER BY t.value DESC)
    FROM YourTable t
    CROSS APPLY (VALUES (
        DATEADD(minute, DATEDIFF(minute, '20000101', t.timestamp), '20000101')
    )) v(StartTime)
) t
WHERE rn <= 2
GROUP BY
  t.StartTime;

db<>fiddle

CodePudding user response：

An easy way to do this is to trim the date down to the minutes then stick that in a temp table and then select max(value), [date stripped down to the minute] from temptable group by [date stripped down to the minute].

This is a simple way of doing it and as you are asking this I assume you want an easy to understand solution.