T-SQL question - add a "number of record" field to groups of data

[PaulSc]

Hi,
I'm after a bit of help/guidance please with a query I have with MS SQL Server (T-SQL)

I have a (large) table containing rows which can be grouped (PtName) and what I'd like to do is update each row and insert a field (Counter) that contains the record number of each field within it's group (i.e each record in its group will be numbered 1 to total number in group)..(Hopefully that's a suitable explanation!)

(This is so that later in the processes [Name] can be updated to be A1_001,A1_002,A1_026,B8_001 etc)

i.e

[pre]Name PtName Counter
A1a A1
A1b A1
A1c A1
... A1
A1z A1
B2a B2
B2b B2
D3a D3
D3b D3
D8a D8
[/pre]

would after update have

[pre]Name PtName Counter
A1a A1 1
A1b A1 2
A1c A1 3
... A1 ...
A1z A1 26
B2a B2 1
B2b B2 2
D3a D3 1
D3b D3 2
D8a D8 1
etc
[/pre]

Can this be done either via a query(s)/stored procedure..?

I can see that the Row_Number() OVER does something similar to what I want but I'd like it to be "reset" as each group is processed..

*EDIT Ok I've looked further into ROW_NUMBER and by using "ROW_Number() OVER (PARTITION by PtName Order By Name)" it gives me the expected results, but (still) interested in possible alternatives/confirmation if this is the correct way to go please if that's ok...

I've got it working in Access vba using various loops but having converted the back end to SQL and whilst the queries still work on the linked tables I've now been asked to utilise pure SQL functions (called initially via Pass-thru queries) as the Access FE is due to be replaced by a web app at some stage and they then just want to plug in the SQl functions that will already be in place....

Any questions please ask and any help/pointers gratefully accepted..

TIA

PaulSc

 

[Olaf Doschke]

This is the usual way to do this, if you're okay with a dynamic current numbering of data within the query result.

If you want to have a sequence of numbers per Ptname in the underlying table obviously integer identity will not offer that, it will need something like an insert trigger or would specify a scalar valued user defined function as the default value of such a column, which then would determine whether you start a new PtName needing initial value 1 or add new records to an already existing PtName, needing current max + 1.

The decision is not a technical one, it can be legitimate to store such a sequence number, or to only generate it while querying data. Obviously reasons to store the sequence number permanently and not generate it on the fly are, if PtName and Counter would be used as a compound key and also stored as foreign key in other tables. Something, which I would not prefer or recommend, but what might be in place already, when you pick up a project, which makes it a fair move to stick with that minor problem instead of causing a more major than minor rewrite. Just think about how much join conditions need to be rewritten changing from a compound to a surrogate primary/foreign key.

So your answer depends on what you need. I hope you only want that Count column for a display/report of the current state of the data and don't need this to get a unique (Ptname,Counter) tuple also for future reference when rows might be deleted and/or added and the Counter would get differing values, when reassigned via ROW_NUMBER(). The way you created this counter row in Access points towards only numbering rows for the moment. Then that's the way to go about this.

Bye, Olaf.

 

[PaulSc]

Olaf,
Thanks for this, it helps a lot..

To provide a bit more context I'm not too worried about maintaining the values as this is part of a process to rename image files (name) stored inside folders (ptName) and its for an ad-hoc process to re-sequence them

i.e

LEMansCars\Porsche.jpg would be renamed LeMansCars_001 and
LEMansCars\LeMansCars_001.jpg would be renamed LeMansCars_002 and
LEMansCars\MercedesC11333u0u97t3763.jpg would be renamed LeMansCars_003 etc

WW2Fighters\Spitfire.jpg would be renamed WW2Fighters_001 and
WW2Fighters\ME262jetfighter.jpg would be renamed WW2Fighters_002 etc

Over time files/folders will be added and housekeeping done to remove duplicates/errors/move to correct folder etc etc resulting in gaps in the numbering, so this process is to enable it to be reset as it actually doesn't matter what the original file name is but they need it to match the folder + a seq number (Sorry I should have explained this better in the first place!)

Anyway thanks again

PaulSc

 

[Olaf Doschke]

Well, thanks for the feedback.

Just some afterthought: In this case redoing the routine of course would add new row numbers to already numbered files. Either you also add a feature to remove numbering of file names, which should be possible, as the files originally were unnumbered and so file names without the final number suffix are also unique.

On the other side you could say the file name are loosely coupled foreign keys to your data and choose to store the numbering as part of the data. Anyway you like it to work.

Bye, Olaf.