CODEWITHSUNDEEP
sql-server
Anand
Anand

Reputation: 1681

SQL Server RANK() start with 1

Suppose I have a table like:

create table t1 (d1 date)

There is no key so dates can repeat. I want to select distinct dates in descending order with rank, such that the rank starts with 1 and sequentially increments.

Date         Rank
----------   ----
2020-01-29   1
2020-01-24   2
2020-01-22   3

I am not an expert in SQL and when I looked up doc on RANK() I found the rank numbers to be non-deterministic.

So I am looking to achieve this in an efficient way with or without RANK().

I already tried as someone asked about ROW_NUMBER()

SELECT DISTINCT
    d1, ROW_NUMBER() OVER (ORDER BY d1) AS Rank  
FROM
    t1 
ORDER BY
    d1 DESC

But this would produce something like:

Date         Rank
----------   ----
2020-01-29   123
2020-01-24   122
2020-01-22   121

Upvotes: 1

Views: 1213

Answers (2)

Jay Rindani
Jay Rindani

Reputation: 40

SELECT d1,
ROW_NUMBER() OVER(ORDER BY d1 desc)AS Rank 
FROM t1

SELECT DISTINCT d1,
RANK() OVER(ORDER BY d1 desc)AS [Rank] 
FROM t1
ORDER BY [Rank]

Upvotes: 2

Squirrel
Squirrel

Reputation: 24763

I think this is what you wanted. As you have duplicate d1 in table, you need to group by the d1 column

select d1, ROW_NUMBER() OVER (ORDER BY d1 DESC) AS [Rank]  
from    t1 
group by d1
order by d1 desc

or if you prefer to use distinct

select  d1, rank() over (order by d1 desc) as [Rank]
from
(
    select  distinct d1
    from    t1 
) d
order by [Rank]

Or dense_rank()

select  distinct d1, dense_rank() over (order by d1 desc) as [Rank]
from    t1
order by [Rank]

Upvotes: 2

Related Questions