0

I'm trying to populate a new Rank field in my table. Here is an example of how it should look like.

+-----------+------------+------+
| CountryId | CreateDate | Rank |
+-----------+------------+------+
|         1 | 2020-01-01 |    4 |
|         1 | 2020-02-01 |    3 |
|         1 | 2020-02-01 |    2 |
|         1 | 2020-03-01 |    1 |
|         2 | 2020-01-01 |    1 |
|         2 | 2020-02-01 |    2 |
|         3 | 2020-01-01 |    1 |
+-----------+------------+------+

So the Rank field has to be populated by count of CountryId and ordered by CreateDate, so the newest record has the highest rank (lowest number).

Note: It can happen that CreateDate is same for two records, in that case the Rank should still increment.

Code:


with rankUpdate as (
      select t.*,
             DENSE_RANK() over (partition by CountryId order by CreatedDate) AS NewRank
      from Countries t
     )
update rankUpdate
     set [Rank] = NewRank;
6
  • RANK() OVER (PARTITION BY CountryId ORDER BY CreationDate DESC)
    – McNets
    Nov 11 '21 at 22:30
  • @McNets The issue with same Date still persist.
    – sharp
    Nov 11 '21 at 22:37
  • It sounds like you're describing a scenario that isn't in your example data--which makes it hard for folks to handle that in answers. It sounds like you're saying that within the country partitions, the date is duplicated, and when that happens, you want the rank to remain unique? Please add that scenario to your data set.
    – AMtwo
    Nov 11 '21 at 22:47
  • 1
    Then use ROW_NUMBER() dbfiddle.uk/…
    – McNets
    Nov 11 '21 at 22:48
  • @AMtwo Yes, date is the same. While writing this question, I wanted to write that I found the ROW_NUMBER to fix my issue.
    – sharp
    Nov 11 '21 at 22:49
3

When dealing with rows that are "tied" during ranking, there are multiple desired outcomes.

RANK will assign "tied" rows the same number, then leave a gap. (Ex 1,2,2,4,4,6,7...)

DENSE_RANK will also assign "tied" rows the same number, but leave no gaps. (Ex 1,2,2,3,3,4,5)

ROW_NUMBER will assign consecutive increasing numbers with no gaps. When two values are "tied" it will randomly determine the order. Note that the randomness is non-deterministic. It might return the same "random" order 10000 times in a row, then suddenly re-order the way it numbers tied rows.

It sounds like you are looking for ROW_NUMBER:

select t.*,
     ROW_NUMBER() over (partition by CountryId order by CreatedDate) AS NewRank
from Countries t

If there's a secondary criteria that can be used as a tie-breaker, you can always add the second column to the ORDER BY. In RANK or DENSE_RANK it can minimize the number of ties, and in ROW_NUMBER it can improve determinism.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.