How to increment an integer based on a group in SQL

Question

Here's my query:

IF Object_id('tempdb..##delist') IS NOT NULL
  DROP TABLE ##delist
SELECT jl.jobid as 'JobID',
       jl.CustomObjectName as 'DEName',
       cols.name as 'ColumnName',
       null as 'ColumnNumber'
INTO   ##delist
FROM   ##joblist AS jl WITH (nolock)
       JOIN sys.tables tbls with (nolock) on tbls.name = jl.CustomObjectName
       JOIN sys.columns cols with (nolock) on cols.object_id = tbls.object_id
WHERE cols.name <> '_CustomObjectKey'

Here's my result set:

What I'm ultimately looking for is a way to number the ColumnNumber data fields incrementally BY "DEName". So in other words, for every ColumnName for each DEName, I want the ColumnNumber to start at 1, and go to however many unique ColumnNames there are for each DEName. Any ideas?

Sight detour...https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ That nolock hint is far more sinister than most people realize. — Sean Lange, May 01 '18 at 20:01
Possible duplicate of [How to return a incremental group number per group in SQL](https://stackoverflow.com/questions/14359749/how-to-return-a-incremental-group-number-per-group-in-sql) — Tab Alleman, May 01 '18 at 20:18
Sorry, on further inspection, I flagged a poor duplicate.. this is a better duplicate: https://stackoverflow.com/questions/2066178/is-it-possible-to-add-a-identity-to-a-group-by-using-sql — Tab Alleman, May 01 '18 at 20:22

score 5 · Accepted Answer · answered May 01 '18 at 19:55

5

You can use row_number():

select . . .,
       row_number() over (partition by jl.CustomObjectName order by (select NULL)) as columnNumber

answered May 01 '18 at 19:55

Gordon Linoff

1,242,037
58
646
786

OP _may_ also want to add `jl.jobid` to the `PARTITION BY` if there is a possibility that a `jl.CustomObjectName` can appear in multiple `jl.jobid`s. – Shawn May 01 '18 at 20:57
@Shawn . . . That is a good point. At some point we do need to trust what an OP says, though: "What I'm ultimately looking for is a way to number the ColumnNumber data fields incrementally BY "DEName". " – Gordon Linoff May 01 '18 at 22:06
1

True. I was more commenting for future readers and to point out to the OP that they may not have realized that the numbering could change if `jobID` ended up being a primary partition column. :-) – Shawn May 02 '18 at 02:55

How to increment an integer based on a group in SQL

1 Answers1