Adding running number on groups of records in a table

Question

Post reply

Adding running number on groups of records in a table

SQLMAIN

SSC Eights!

Points: 936
More actions
January 8, 2009 at 7:46 am

#203761

I have a table carrying following sets of records
act, name, num, dt
1 abc 123 2008-01-01
1 abc 123 2008-01-02
1 abc 123 2008-01-03
2 xyz 123 2008-01-01
2 xyz 123 2008-01-02
2 xyz 123 2008-01-03
3 lmn 123 2008-01-01
3 lmn 123 2008-01-02
4 opn 123 2008-01-02
I want these records copied to another table (or do it in place) such that a new column gets added which carries a running number based on the date order for the set where first three columns have the same value. Output must look like this
act, name, num, dt seqno
1 abc 123 2008-01-01 1
1 abc 123 2008-01-02 2
1 abc 123 2008-01-03 3
2 xyz 123 2008-01-01 1
2 xyz 123 2008-01-02 2
2 xyz 123 2008-01-03 3
3 lmn 123 2008-01-01 1
3 lmn 123 2008-01-02 2
4 opn 123 2008-01-02 1
It is to be done on a table carrying 15million rows.
Any help will be appreciated.

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply

gmby SSCarpal Tunnel Points: 4451 More actions · Answer 1

Lookup the ROW_NUMBER function in BOL. It should do the trick for you.

jg

SQLMAIN SSC Eights! Points: 936 More actions · Answer 2

I am on SQLServer2K and it does not seem to recognize the ROW_NUMBER function.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

john g (1/8/2009)
Lookup the ROW_NUMBER function in BOL. It should do the trick for you.
jg

Might take a while to find it in BOL for SQL2k!

This looks like a candidate for the "Running Totals Update" aka "Clustered Index Update".

See here[/url] and here.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 4

See the link in my signature for the standard running totals method. It will solve this no problem, and is the fastest method I've seen to handle these situations. Let us know if you need help with the implementation. If you do, please provide sample data in the way described in the "How to post sample data" link in my sig.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

I have to second Seth on reading the article on Running Totals (also linked in my sig block). The method described in the article can update a 1,000,000 row table in about 6 to 10 seconds depending on your hardware and other processes that may be running.

If you need help, again follow Seth's recommendation, and I'm sure someone will help you out.

gmby SSCarpal Tunnel Points: 4451 More actions · Answer 6

LOL, it has been quite a day so far - 2 bad responses on my part...it has definitely taught me to read the OP carefully - and not in a hurry

jg

Garadin One Orange Chip Points: 29613 More actions · Answer 7

john g (1/8/2009)
LOL, it has been quite a day so far - 2 bad responses on my part...it has definitely taught me to read the OP carefully - and not in a hurry
jg

Happens to us all.

Conversely, if you read it too carefully or take too much time, Chris beats you on all your posts, so it's a tradeoff ;).

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Garadin (1/8/2009)
john g (1/8/2009)
LOL, it has been quite a day so far - 2 bad responses on my part...it has definitely taught me to read the OP carefully - and not in a hurry
jg
Happens to us all.
Conversely, if you read it too carefully or take too much time, Chris beats you on all your posts, so it's a tradeoff ;).

Cutting you some slack from monday Seth, starting a new contract 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 9

Hah! Awesome. By the way, have you considered using that butler from Mr. Deeds as your avatar? Very sneaky :hehe:.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Had to Google it Seth, movies take a while to cross the pond...but "a butler who takes an odd interest in feet" - GF might find that very amusing:)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 11

Think I just found a fitting avatar for myself for that matter.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Garadin (1/8/2009)
Think I just found a fitting avatar for myself for that matter.

Isn't that Muffin?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 13

Lynn Pettis

SSC Guru

Points: 442447

January 8, 2009 at 9:10 am

#923424

hmmm, avatar I too have found

Garadin One Orange Chip Points: 29613 More actions · Answer 14

Chris Morris (1/8/2009)
Had to Google it Seth, movies take a while to cross the pond...but "a butler who takes an odd interest in feet" - GF might find that very amusing:)

The joke doesn't make nearly as much sense if you haven't seen the movie. The butler in the movie is "very sneaky", and keeps appearing randomly whenever he's needed with nobody seeing him coming. Pretty amusing stuff. It's an Adam Sandler movie, and you typically either like his movies or you don't(I normally do), and I thought it was pretty decent. It's been out for several years now, so I'd imagine it'd be available if you were so inclined to watch it =).

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]