CODEWITHSUNDEEP
sqlsql-server
Tart
Tart

Reputation: 305

Rank by day and time

I have next timestamps that I'm trying to assign ranking to:

ID             Time
A2    2019-01-07 14:00:32.0000000
A2    2019-01-07 14:56:02.0000000
A2    2019-01-14 10:37:21.0000000
A2    2019-01-14 10:37:30.0000000

Need to use a day, an hour and minutes as criteria so tried to use:
ROW_NUMBER() OVER (PARTITION BY ID, DAY(Time) ORDER BY Time) AS [RowNBR]

and the result is :

   RowNBR     ID             Time
      1       A2    2019-01-07 14:00:32.0000000
      2       A2    2019-01-07 14:56:02.0000000
      1       A2    2019-01-14 10:37:21.0000000
      2       A2    2019-01-14 10:37:30.0000000

but it does take into account seconds, whereas I need to get the next result (as the last two timestamps are identical if not taking into account seconds):

   RowNBR     ID             Time
      1       A2    2019-01-07 14:00:32.0000000
      2       A2    2019-01-07 14:56:02.0000000
      1       A2    2019-01-14 10:37:21.0000000
      1       A2    2019-01-14 10:37:30.0000000

I can certainly "strip" the seconds before applying ROW/RANK/DENSE function but wondering if there is a way how to assign this logic within ROW/RANK/DENSE functions.

Upvotes: 1

Views: 680

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Here is one method:

select rank() over (partition by id, convert(date, time)
                    order by datepart(hour, time), datepart(minute, time)
                   ) as ranking

Upvotes: 2

Related Questions