What's wrong with this Partition By

I have a query that uses a partition by over a time column, however the result is a bit unexpected, what's wrong here ? Why do I get more than one 1 on RN ? (one for 21:00:02:100 and the other for 21:00:02:600)

SELECT TOP 500
    ROW_NUMBER() OVER(
                PARTITION BY [Date], CAST([Time] AS Time(0))
                ORDER BY [DATE] ASC, CAST([Time] AS Time(0)) ASC
               ) RN,
               [DATE],
               [Time]               
FROM [DB]..[TABLE]
ORDER BY [Date] ASC,
         [Time] ASC,
         [RN] ASC

Results:

**1 2010-10-03  21:00:02.100**
2   2010-10-03  21:00:02.100
3   2010-10-03  21:00:02.200
4   2010-10-03  21:00:02.200
5   2010-10-03  21:00:02.200
4   2010-10-03  21:00:02.500
**1 2010-10-03  21:00:02.600**
2   2010-10-03  21:00:02.600
3   2010-10-03  21:00:02.600
5   2010-10-03  21:00:02.700
6   2010-10-03  21:00:02.700
7   2010-10-03  21:00:02.700
8   2010-10-03  21:00:02.700
9   2010-10-03  21:00:02.700
10  2010-10-03  21:00:02.700
11  2010-10-03  21:开发者_StackOverflow00:02.700
12  2010-10-03  21:00:02.700
13  2010-10-03  21:00:02.700
14  2010-10-03  21:00:02.700
15  2010-10-03  21:00:02.700
16  2010-10-03  21:00:02.700
17  2010-10-03  21:00:02.700
18  2010-10-03  21:00:02.700
19  2010-10-03  21:00:02.700
20  2010-10-03  21:00:02.700
21  2010-10-03  21:00:02.700
22  2010-10-03  21:00:02.700

You are using CASTing to time(0) for your ordering which rounds (truncates?) the time to second precision. It is working exactly as advertised...

Edit:

It makes no sense to have the same PARTITION BY and ORDER BY...

My guess is that you are trying to partition by the second, and want rows numbers in that interval

Try this:

ROW_NUMBER() OVER(
            PARTITION BY [Date], CAST([Time] AS Time(0))
            ORDER BY [DATE], [Time]
           ) RN

If you get duplicates row numbers crossing the 0.5 second boundary, use this to force truncate rather then ROUND

ROW_NUMBER() OVER(
            PARTITION BY [Date], CAST([Time] - '00:00:00.5000' AS Time(0))
            ORDER BY [DATE], [Time]
           ) RN

Thanks a lot for your feedback, turns out that cast is rounding it and therefore its not working (giving me two times the 1). Subtracting from [TIME] didn't work for me, got an error. At the end I used this code to get it working as wanted:

ROW_NUMBER() OVER( PARTITION BY CONVERT(nvarchar(8), [Time], 8) ORDER BY [Date], [Time]) RN FROM [DB]..[TABLE]