DENSE RANK with offset of numbers

Transact-SQL
1

I've been searching for about half an hour trying to work this out and thought it would be easy to do.

I have a dense rank in my script:

,DENSE_RANK() OVER (ORDER BY ISNULL(a.[Country Name],''),a.[City]) + 3 AS CityRankRow

Which I need to start from number 4 so did + 3

so I get:

	London	4
	London	4
	London	4
	Madrid	5
	Paris	6
	Paris	6
	Paris	6
	Paris	6

What I now need is Dense Rank to increment not by 1 but 3 each time so I get:

	London	4
	London	4
	London	4
	Madrid	7
	Paris	10
	Paris	10
	Paris	10
	Paris	10

Am I missing something here, thought it would be easy to do but I'm going in circles.

2 
(DENSE_RANK() OVER (ORDER BY ISNULL(a.[Country Name],''),a.[City])-1)*3 + 4
1 Like
3

Hi JamesK,

That is friggin genius, it works!

Thanks,
Roger