CODEWITHSUNDEEP
sqlsql-servert-sql
harshadbhatia
harshadbhatia

Reputation: 234

ranking on same date sql

Table 1:
id  |       date      | value
1    15/04/2013 15:45    1
1    15/04/2013 15:55    1
2    16/04/2013 15:45    2
2    16/04/2013 15:55    2
3    17/04/2013 15:25    3
3    17/04/2013 15:35    3

After using query 

Select * 
from (
    Select *, ROW_NUMBER() OVER (Partition by id, date ORDER BY date DESC) from table X
) A

It RANKS all the rows properly except the rows which appear on same day but different times.

In other words i want to write sql which can rank on date and then on its time.

Upvotes: 0

Views: 1545

Answers (2)

peterm
peterm

Reputation: 92785

Are you looking for this?

SELECT a.* 
FROM (
    SELECT x.*, ROW_NUMBER() OVER (PARTITION BY id, DATEADD(dd, 0, DATEDIFF(dd, 0, date)) ORDER BY date DESC) rownum
      FROM table1 x
) A

Sample output:

| ID |                         DATE | VALUE | ROWNUM |
------------------------------------------------------
|  1 | April, 15 2013 15:55:00+0000 |     1 |      1 |
|  1 | April, 15 2013 15:45:00+0000 |     1 |      2 |
|  2 | April, 16 2013 15:55:00+0000 |     2 |      1 |
|  2 | April, 16 2013 15:45:00+0000 |     2 |      2 |
|  3 | April, 17 2013 15:35:00+0000 |     3 |      1 |
|  3 | April, 17 2013 15:25:00+0000 |     3 |      2 |

Here is SQLFiddle demo

Upvotes: 1

Deepak Saralaya
Deepak Saralaya

Reputation: 457

try

order by convert(datetime, date, 103) DESC

Upvotes: 0

Related Questions