January 8, 2009 at 7:46 am
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.
January 8, 2009 at 7:55 am
Lookup the ROW_NUMBER function in BOL. It should do the trick for you.
jg
January 8, 2009 at 8:03 am
I am on SQLServer2K and it does not seem to recognize the ROW_NUMBER function.
January 8, 2009 at 8:14 am
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
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
January 8, 2009 at 8:16 am
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.
January 8, 2009 at 8:30 am
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.
January 8, 2009 at 8:30 am
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
January 8, 2009 at 8:33 am
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 hurryjg
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 ;).
January 8, 2009 at 8:56 am
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 hurryjg
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 😀
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
January 8, 2009 at 8:59 am
Hah! Awesome. By the way, have you considered using that butler from Mr. Deeds as your avatar? Very sneaky :hehe:.
January 8, 2009 at 9:06 am
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:)
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
January 8, 2009 at 9:07 am
January 8, 2009 at 9:10 am
Garadin (1/8/2009)
Think I just found a fitting avatar for myself for that matter.
Isn't that Muffin?
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
January 8, 2009 at 9:10 am
hmmm, avatar I too have found
January 8, 2009 at 9:11 am
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 =).
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply