INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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

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

would after update have

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
 

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

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

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.

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

(OP)
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

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close