SQL Generate Sequence Numbers

Question

I have a table with 2 PK Columns that I need to populate. The first column "ID" is already populated, I need to generate and populate the "SeqNum" column with int's like in my example below. There obviously cannot be the same SeqNum in the same record as the same ID because they are both PK's(I'm sure you know what I mean). How can I accomplish this?

It is a simple query like so:

Insert into @TempTable
Select A.ID
      ,1 --not sure what to do here
      ,B.Col3
      --and other columns
From Blah A Join Blah2 B on B.ID = A.ID

I've tried something like and it didn't work:

(Select max(ISNULL(SeqNum, 0)) + 1 From @TempTable Where ID = A.ID)

Any help is very much welcomed. Maybe I have to loop?

Example output I need:

If we assume this is SQL server or postgresql then John's answer seems to fit nicely. — xQbert
– xQbert, Commented Dec 29, 2016 at 21:56
Agreed. If zanq was looking for a list of N items, it would be different. — Kevin
– Kevin, Commented Dec 29, 2016 at 22:01
Added sql-server tag based on the (non-standard) syntax used in the accepted answer — user330315
– user330315, Commented Dec 29, 2016 at 22:33

John Cappelletti · Accepted Answer · 2016-12-29 21:56:04Z

7

If I understand the question, Row_Number() would be a good fit here

Select ID
       ,SeqNum = Row_Number() over (Partition By ID Order By (Select NULL))
 From  ...

answered Dec 29, 2016 at 21:56

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zanq Over a year ago

YES! Thank you! It is exactly what I need!

John Cappelletti Over a year ago

@zanq Happy to help. It would be well worth your time to get comfortable with with Window Functions. They can be invaluable. cheers

Collectives™ on Stack Overflow

SQL Generate Sequence Numbers

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related