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

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.

CodePudding user response：

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;