Jeff Moden

Forum Replies Created

Viewing 15 posts - 34,696 through 34,710 (of 59,099 total)

RE: Displaying Sorted Hierarchies (SQL Spackle)

sean-495500 (3/10/2011)
Nice article Jeff. I see that you promised a more in-depth/advanced discussion within the body of your article. Are you going to be discussing the HierarchyID data...
--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)

March 10, 2011 at 7:09 pm

#1297417
RE: Displaying Sorted Hierarchies (SQL Spackle)

CirquedeSQLeil (3/10/2011)
Nice article Jeff. I have used a very similar method to this for very large hierarchies. Nice examples and well explained and illustrated.
Thank you, Jason. Since...
--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)

March 10, 2011 at 7:00 pm

#1297414
RE: Displaying Sorted Hierarchies (SQL Spackle)

gregg_dn (3/10/2011)
Great article Jeff.
Thanks for stopping by, Gregg. I appreciate the compliment.
--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)

March 10, 2011 at 6:56 pm

#1297413
RE: Displaying Sorted Hierarchies (SQL Spackle)

TheSQLGuru (3/10/2011)
I use 'generated' TSQL like that all the time but never thought to make the leap and intermix straight/generated TSQL in a CTE like that. Oh the possibilities!!...
--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)

March 10, 2011 at 6:53 pm

#1297412
RE: How to get prev and next record from a selection

WayneS (3/10/2011)
and probably more efficient than correlated sub-queries.
There's only one way to know, huh? 😉
--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)

March 10, 2011 at 1:30 pm

#1297345
RE: Displaying Sorted Hierarchies (SQL Spackle)

Heh... Gus beat me to it but he's, apparently, actually done some testing.
(Note to self... need performance testing code proofs in the article I'm writing.) 😛
--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)

March 10, 2011 at 1:24 pm

#1297342
RE: Displaying Sorted Hierarchies (SQL Spackle)

sean-495500 (3/10/2011)
Using HierarchyID Datatype with this solution
If added a new column into the table called OrganisationLevel with a HierarchyID datatype, then updated this column with the Hierarchy data that 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)

March 10, 2011 at 1:21 pm

#1297340
RE: Displaying Sorted Hierarchies (SQL Spackle)

CELKO (3/10/2011)
Jeff, GSquare, et al
I am doing the second edition of TREES & HIERARCHIES this year. If you have stuff that ought to be in the book, plese send it...
--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)

March 10, 2011 at 11:29 am

#1297269
RE: Displaying Sorted Hierarchies (SQL Spackle)

CELKO (3/10/2011)
I am doing the second edition of TREES & HIERARCHIES this year. If you have stuff that ought to be in the book, plese send it to me ASAP....
--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)

March 10, 2011 at 11:26 am

#1297268
RE: Return records at least 10 minutes apart from last valid record without CURSOR

Rob Reid-246754 (3/8/2011)
I totally get what you mean but its pretty hard to update say the FormRating for all runners in a race at one time as there are so...
--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)

March 10, 2011 at 8:48 am

#1297147
RE: Without using CTE, Please provide a recursive method

Bhuvnesh (3/8/2011)
hi varun, can u tell us any specific reason why you don't want to use CTE?if the other approach the comparatively expensive.
He already did...
Dont use CTE because limit exceeds
I...
--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)

March 10, 2011 at 8:46 am

#1297144
RE: Complex Query

BeginnerBug (3/9/2011)
Hello Jeff... i implemented it in my coding which has more columns..it works fine.. is there any other way to make it simple?:-)
Adding more columns isn't the problem I...
--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)

March 10, 2011 at 8:44 am

#1297140
RE: How to get prev and next record from a selection

[Jim].[dba].[Murphy] (3/10/2011)
Chazman - nice work. The min select should be aliased as NextID instead. Minor typo.
I agree with Chazman - although this will work, it is not a nice,...
--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)

March 10, 2011 at 8:10 am

#1297114
RE: Displaying Sorted Hierarchies (SQL Spackle)

GSquared (3/10/2011)
I've got a two-step script that can generate a nested sets "path" from an adjacency hierarchy, turning it into a nested sets hierarchy, using Cross Apply and a ranking...
--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)

March 10, 2011 at 8:01 am

#1297110
RE: Displaying Sorted Hierarchies (SQL Spackle)

cedarhillr (3/10/2011)
If one used the same method but inverted the list you have a roll up you can use for various accounting functions. Inverted list processing was one of...
--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)

March 10, 2011 at 7:30 am

#1297095

Viewing 15 posts - 34,696 through 34,710 (of 59,099 total)