Home Add one to every value in a column each time that it exceeds the numbers in a set?

Questions

Add one to every value in a column each time that it exceeds the numbers in a set?

February 3, 2022

I’m dealing with a ranking crisis. Imagine some data like this:

Data	Rank
Alice	10
Bob	25
Claire	33
Dale	42
Ebony	56
Fed	68

And imagine that I have a set of values: 13, 35, and 49. I want to go through my set of ranks and add one to each rank for each time that it exceeds a value in this set. This means that my final output will be worked out as:

Data	Rank
Alice	10 + 0 = 10
Bob	25 + 1 = 26
Claire	33 + 1 = 34
Dale	42 + 2 = 44
Ebony	56 + 3 = 59
Fed	68 + 3 = 71

How can this be achieved? I don’t want to write a cursor unless I really have to. I suspect that joins can solve this somehow.

>Solution :

Filling in the blanks a little, but I assume you could likely just use a LEFT JOIN and COUNT here:

SELECT YT.Data,
       YT.Rank + COUNT(OT.OtherValue) AS Rank
FROM dbo.YourTable YT
     LEFT JOIN dbo.OtherTable OT ON YT.Rank > OT.Othervalue
GROUP BY YT.Data,
         YT.Rank;