CODEWITHSUNDEEP
sqlsql-server
Brent Theunckens
Brent Theunckens

Reputation: 105

Auto number after reset count for each different column value

I am using SQL Server management studio 2014.

I have 2 tables: Country and locations.

Country has a property Id and other columns.

Locations has a property Id, LocationNumber, CountryId.

In the old application it saved the data wrong for locationNumber when you would delete one.

So I am trying to reset all the location numbers and insert it again.

Let's assume I have this dataset:

Locations:

Id | LocationNumber | CountryId
-------------------------------
1  |        1       |     1
2  |        3       |     1
3  |        1       |     2
4  |        4       |     2

Now I want to reset the locationNumber per countryId so it would become this dataset:

Id | LocationNumber | CountryId
-------------------------------
1  |        1       |     1
2  |        2       |     1
3  |        1       |     2
4  |        2       |     2

It should be done with a query and not with increment properties.

Thank you, Brent

Upvotes: 0

Views: 94

Answers (1)

Sergio Prats
Sergio Prats

Reputation: 1213

You can try this query:

Select Id, ROW_NUMBER() OVER(PARTITION BY CountryId ORDER BY LocationNumber ) 
AS LocationNumber, CountryId FROM Locations

Upvotes: 1

Related Questions