Re-numbering a non-unique column in a SQL Server table

Question

I'm trying to renumber a column named Count, in a SQL Server 2019 table.

The layout is like this:

date	Location	Store	Count
2023-02-01	AU	Store1	1
2023-02-01	AU	Store1	3
2023-02-01	AU	Store1	4
2023-02-01	UK	Store1	2
2023-02-01	UK	Store1	4
2023-02-01	UK	Store1	5

I am trying to renumber the Count column, so, that for each distinct Date, Location and Store, the numbers for the Count column are contiguous, for that given Date, Location and Store.

For example, for the 2023-02-01|AU|Store1, the count should be changed from 1,3,4 to 1,2,3.

For the UK store for the same date should be changed from 2,4,5 to 1,2,3.

The count should always begin with one, for the first row for a given Date, Location and Store.

I thought it would be relatively easy, but my head is hurting.

Any suggestions greatly appreciated.

Answer 1

Just throwing this out there as what perhaps what you think you want:

update n set Count = Newcount
from (
  select *, Row_Number() over(partition by date, location, store order by count) Newcount
  from mytemptable
)n;

I think you should be implementing a view to include the count column instead of having it as a column in your table, then querying the view will always have the correct count.

create view Myview as
select *, Row_Number() over(partition by date, location, store order by <your ordering criteria>) Count
from mytemptable;

Then just use the view instead of mytemptable;