CODEWITHSUNDEEP
sqlsql-server
nam vo
nam vo

Reputation: 3437

select query based on priority

I have table 

Id  createdonutc 
1    2014-05-29 10:14:00.240
2    2014-05-29 10:16:58.587
3    2014-06-01 11:32:54.140
4    2014-05-29 10:48:40.427
6    2014-06-01 2:1:54.140

I want to get the data with orderby date only(no time) desc

Id  createdonutc                    rank

3    2014-06-01 11:32:54.140         1
6    2014-06-01 2:1:54.140           1
1    2014-05-29 10:14:00.240         2
2    2014-05-29 10:16:58.587         2
4    2014-05-29 10:48:40.427         2

i tried:

SELECT
    id, 
    P.createdonutc,  
    ROW_NUMBER() OVER (PARTITION BY cast(p.[CreatedOnUtc] AS DATE) ORDER BY cast(p.[CreatedOnUtc] AS DATE) desc) as num
FROM
    news p
ORDER BY num

it shows no change on the result.How can i get it to work ?

Upvotes: 0

Views: 85

Answers (2)

Serpiton
Serpiton

Reputation: 3684

Row_Number generate a different number for every row, even when the values are equal, to get what you want you need to use DENSE_RANK

SELECT id, createdonutc
     , dense_rank() OVER (order by cast(createdonutc as date) DESC)
FROM   News

SQLFiddle demo

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try using DENSE_RANK():

SELECT
    id, 
    P.createdonutc,  
    Dense_Rank() OVER (ORDER BY cast(p.[CreatedOnUtc] AS DATE) desc) as num
FROM
    news p
ORDER BY num

Upvotes: 2

Related Questions