Hidden RBAR: Counting with Recursive CTE's

Question

Post reply

Hidden RBAR: Counting with Recursive CTE's

Viewing 14 posts - 61 through 73 (of 73 total)

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

lstrashnoy Valued Member Points: 61 More actions · Answer 1

No, I use custom stored function for that purpose.

-Leonard

Bill Kline-270970 SSC Eights! Points: 915 More actions · Answer 2

Excellent article. A great example of why I come to SQLServerCentral.com . . . to learn from the best.

Kenney Hill SSC Eights! Points: 923 More actions · Answer 3

Jeff Moden (8/10/2011)
Paul Herbert (8/10/2011)
As usual a good article Moden.
A minor correction if you don't mind. If you are going to quote my 'Really?!", I think it needs both a question mark & an exclamation point. That way it reads like I say it ... a touch of amusement & a ton of WTF. 🙂
Everyone... please meet and greet Mr. Paul Herbert... the Systems DBA where I work at. We've slain many dragons together in the short year or so that we've had the opportunity to work with each other. 🙂 I've been trying to convince him to write an article about some of the things he's done on the Administrative side of the world. Maybe you good folks can help me convince him. 😉

Welcome Paul!

I'd really be interested in an administrative article by you. The administrative side is my primary SQL job focus, but I know there are so many things that I could set-up better on that side.

Plus you could always let slip something embarrassing or funny or both about Jeff in the article!:hehe:

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 4

Bill Kline-270970 (8/11/2011)
Excellent article. A great example of why I come to SQLServerCentral.com . . . to learn from the best.

Thanks, Bill. :blush: I really appreciate the compliment but, gosh no... I'm not in "the best" category. There are plenty of folks that are much better at this than I. I just try really hard to keep up with all of them. 🙂

--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)

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 5

Kenney Hill (8/11/2011)
Plus you could always let slip something embarrassing or funny or both about Jeff in the article!:hehe:

Heh... the fact that I talk with the dust bunnies isn't bad enough? 😛

--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)

David Burrows SSC Guru Points: 65141 More actions · Answer 6

Another great article Jeff 🙂

Jeff Moden (8/8/2011)
... some would say that I, indeed, have no taste buds

Surely you meant 'no taste' 😛

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 7

David Burrows (8/19/2011)
Another great article Jeff 🙂
Jeff Moden (8/8/2011)
... some would say that I, indeed, have no taste buds
Surely you meant 'no taste' 😛

heh... that, too! And don't call me "Shirley". 😛

--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)

Gullimeel Hall of Fame Points: 3871 More actions · Answer 8

Nice article...

Using tally( or numbers table) is best way..Easy to code :).

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

j-1064772 SSCertifiable Points: 5316 More actions · Answer 9

Jeff Moden
Beer has some interesting freezing qualities... take it down to somewhere between 26 and 28^oF and it's still liquid. Give it a little bit of a sharp rap and all the beer in the bottle will turn to ice in just several seconds.
The "Hot Ice" trick requires a chemical that you can make from regular household items like vinegar and (IIRC) baking soda and (IIRC) Isopropyl alcohol which, of course, is poison to drink. Makes a really cool (no pun intended) effect though. Hmmm... I wonder if it'll still work with Ethanol.

[font="Comic Sans MS"]It does not have to be beer. Supercooled water can be produced - you need distilled water. You probably will not be able to do this in a freezer, the mechanical vibrations from the compressor would be equivalent to the shaking action you want to create yourself to be able to observe the spontaneous formation of ice. You have to use a cold bath of salty water brought down to below the freezing temperature of pure water. What happens is that shaking creates nucleation sites where the phase can change to solid ice.

I just completed viewing all of prof. Grossman's Thermodynamics DVD lectures - this was just one of his many demos.

[/font]

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 10

I've actually had the phenomena occur in my truck overnight in the winter months both with bottled spring water and Pepsi. It's amazing how cold it was (~10^o-15^oF) and it was still liquid.

--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)

j-1064772 SSCertifiable Points: 5316 More actions · Answer 11

[font="Comic Sans MS"]Ah but that was a MIXTURE, not pure water. Even CO₂ dissolved in water is a mixture.

And for an amazing development, check out NITINOL - a shape memory compound which is "trained" for a particular shape at low temperature and another shape at high temperature - just watch it change shape with a change in the ambient temperature (also from Grossman's DVD)

Totally off-topic (you got me stated with the liquid ice and Rosetta stone). OK enough diversion from T-SQL[/font]

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 12

j-1064772 (6/23/2015)
[font="Comic Sans MS"]Ah but that was a MIXTURE, not pure water. Even CO₂ dissolved in water is a mixture.
And for an amazing development, check out NITINOL - a shape memory compound which is "trained" for a particular shape at low temperature and another shape at high temperature - just watch it change shape with a change in the ambient temperature (also from Grossman's DVD)
Totally off-topic (you got me stated with the liquid ice and Rosetta stone). OK enough diversion from T-SQL[/font]

Been working with various flavors of Nitinol and building electronics to get it to do certain things since 1993. Also worked a brief period with the cryogenic version of pipe connections. Fun stuff.

--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)

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 13

At the risk of sounding like a crazy stalker, I'm glad I started "following" you on this forum Jeff, I can usually guarantee that a thread you pop up on will reference something insightful, interesting and above all useful. Worth the overhead of extra email I have in the morning 😀

I'd not seen this "old" article before, but it's one of those eye-openers that have really helped my career. Thanks (again).

"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 14

david.edwards 76768 wrote:

At the risk of sounding like a crazy stalker, I'm glad I started "following" you on this forum Jeff, I can usually guarantee that a thread you pop up on will reference something insightful, interesting and above all useful. Worth the overhead of extra email I have in the morning 😀
I'd not seen this "old" article before, but it's one of those eye-openers that have really helped my career. Thanks (again).

Gosh... thank you for the very kind words and for the great feedback on this article, David. I do sometimes wonder "am I helping" and so posts like this one are greatly appreciated. You've made my day. Heh... I need more "stalkers" like you. 😀

--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)