How SQL Server groups numbers

We often have this requirement in our production practice: Group numbering.

There is a City area table region as follows:

We need to group the above table region by city and sort the region to get the following result:

The specific SQL is as follows:

1 Select  2Right ('+'+row_number ()over by  order by region),2as3 from Region

This method mainly uses the over function by grouping the city, then sorting the region (by default alphabetical alphabetical order), and finally get the result we want.

Of course, there is another way to do the same:

Select City,region,region_no =right (+ +(selectCount(1)+1fromwhere City= and Region<a.region),2) from Region A

If other small partners have better ideas, welcome to the bottom of the message, we learn from each other, common progress, thank you!

How SQL Server groups numbers