Adding running number on groups of records in a table

Question

Adding running number on groups of records in a table

Viewing 15 posts - 16 through 30 (of 38 total)

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

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

Chris Morris (1/8/2009)
Garadin (1/8/2009)
Think I just found a fitting avatar for myself for that matter.
Isn't that Muffin?

My turn to Google! Never heard of Muffin the Mule before... but no, that's not exactly what I was going for with that one.

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 2

Lynn Pettis (1/8/2009)
hmmm, avatar I too have found

Gits unite old, yes!

^{“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 3

hmmm, hijacked this thread I think, back to topic I think we should...

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

Garadin (1/8/2009)
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 =).

Heh I'll look for it in Woolworths...Zavvi...I'll download it! Cheers Seth.

^{“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

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

Garadin (1/8/2009)
Chris Morris (1/8/2009)
Garadin (1/8/2009)
Think I just found a fitting avatar for myself for that matter.
Isn't that Muffin?
My turn to Google! Never heard of Muffin the Mule before... but no, that's not exactly what I was going for with that one.

Joking aside IMO it's a brilliant choice. Apart from anything else, the emphasis on teaching will give you quite a bit of leeway with the "give a man a fish" scenario. We've all been there, Jeff's had a particularly nasty one recently.

^{“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

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

Here is the test data for the situation...

select cast('a' as varchar(5)) act,

cast(1 as tinyint) nbr,

cast('nick' as varchar(10)) name,

cast('2008-01-01' as smalldatetime) dt

into tmptst

insert into tmptst values('a', 1, 'nick', '2008-01-02')

insert into tmptst values('a', 1, 'nick', '2008-01-03')

insert into tmptst values('a', 2, 'nick', '2008-01-05')

insert into tmptst values('b', 1, 'nick', '2008-01-01')

insert into tmptst values('b', 1, 'nick', '2008-01-02')

insert into tmptst values('b', 1, 'nick', '2008-01-03')

insert into tmptst values('b', 2, 'nick', '2008-01-05')

insert into tmptst values('c', 1, 'nick', '2008-01-01')

insert into tmptst values('c', 1, 'nick', '2008-01-02')

insert into tmptst values('c', 1, 'nick', '2008-01-03')

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

Have you read the Article 'Solving the "Running Totals" & "Ordinal Rank" Problems in SS 2K/2K5'? It is the second link in my signature block below. If you haven't read, please read it now. If, after reading it, you still have questions on how to implement the solution let us know.

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

This works for me - let me know if you see some thing wrong in here...

drop table tmptst

select cast('a' as varchar(5)) act,

cast(1 as tinyint) nbr,

cast('nick' as varchar(10)) name,

cast('2008-01-01' as smalldatetime) dt,

CAST(NULL AS INT) GRPCNT

into tmptst

insert into tmptst values('a', 1, 'nick', '2008-01-02', null)

insert into tmptst values('a', 1, 'nick', '2008-01-03', null)

insert into tmptst values('a', 2, 'nick', '2008-01-05', null)

insert into tmptst values('b', 1, 'nick', '2008-01-01', null)

insert into tmptst values('b', 1, 'nick', '2008-01-02', null)

insert into tmptst values('b', 1, 'nick', '2008-01-03', null)

insert into tmptst values('b', 2, 'nick', '2008-01-05', null)

insert into tmptst values('c', 1, 'nick', '2008-01-01', null)

insert into tmptst values('c', 1, 'nick', '2008-01-02', null)

insert into tmptst values('c', 1, 'nick', '2008-01-03', null)

CREATE CLUSTERED INDEX IX_key

ON dbo.tmptst (Act, nbr, name, Dt)

DECLARE @PrevGrpCnt INT --Running count resets when account changes

SET @PrevGrpCnt = 0

DECLARE @PrevAct varchar(5) --The "anchor" and "account change detector"

SET @PrevAct = ''

DECLARE @PrevName varchar(5) --The "anchor" and "account change detector"

SET @PrevName = ''

DECLARE @PrevNbr tinyint --The "anchor" and "account change detector"

SET @PrevNbr = 0

UPDATE dbo.tmptst

SET --===== Running Total

@PrevGrpCnt = GrpCnt = CASE WHEN Act = @PrevAct and @Prevname = name and @Prevnbr = nbr

THEN @PrevGrpCnt + 1

ELSE 1 -- Restarts count at "1"

END,

--===== "Anchor" and provides for "account change detection"

@Prevact = Act,

@Prevname = name,

@Prevnbr = nbr

FROM dbo.tmptst

WITH (INDEX(IX_key),TABLOCKX)

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

hmmm, got it you do

I don't have time at the moment to actually test your code, but a quick check of the code looks good. I'll try to double check it later unless someone else beats me to it.

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

[Edited]

Looks like you've got it. How long does it take to run against your 15M rows?

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]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Chris Morris (1/8/2009)
Apart from anything else, the emphasis on teaching will give you quite a bit of leeway with the "give a man a fish" scenario. We've all been there, Jeff's had a particularly nasty one recently.

Which post are you talking about? :pinch:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

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

I did not really time it as there was no need..:) it completed without causing any concerns. May be a minute or so.

Thanks guys for pointing me to the article and making me think a little bit rather than just giving a solution.

Thanks - learnt a new technique.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

SQLMAIN (1/8/2009)
May be a minute or so.

That would be about right for 15M rows.

Just a bit of caution, though... make sure you follow ALL the instructions in the article. Forgetting just one of them can give you some really bad data. I have to say it "out loud" because so many people have taken a shortcut here and there or simply forgotten a "step" in the article and BOOM! Instant bad data... don't be afraid to use the technique but do be very afraid if you don't do it right. 😉

The example code you wrote above looks like you've included everything.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

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

Jeff Moden (1/8/2009)
Chris Morris (1/8/2009)
Apart from anything else, the emphasis on teaching will give you quite a bit of leeway with the "give a man a fish" scenario. We've all been there, Jeff's had a particularly nasty one recently.
Which post are you talking about? :pinch:

This one...

^{“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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 15

Heh... ah yes... THAT one. And because I'm a professional, I'm also supposed to be nice in the face of such abuse. I rewrote that one about 5 times trying to be nice. What it really needed was a barrage of high speed pork chops. 😛

How'd I do? :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions