Linking to the Previous Row

Question

Linking to the Previous Row

Viewing 15 posts - 31 through 45 (of 147 total)

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

Paul Hough SSC Enthusiast Points: 118 More actions · Answer 1

Hi

The current method we use is of course using a # temp table with an Identity column added with an ORDER BY clause. (The only(?) time one should use an ORDER BY when writing into a table).

The advantage of this approach is that for a large set the temp table can be suitably indexed for joining the previous and next rows (oh, and a further advantage is that this can be used in previous versions of SQL Server), but a disadvantage is that this cannot be used in a view.

Paul Hough

sara karasik SSCommitted Points: 1573 More actions · Answer 2

Thanks for the info on CTE's.

Has anyone compared the speed of CTE's vs. temporary tables vs. a table variable ?

Can a CTE do more than a temp table or a table variable?

Or are CTE's just a cosmetic addition?

David McKinney SSChampion Points: 10472 More actions · Answer 3

Well, they're not purely cosmetic. Specifically they enable you to write recursive queries - which I don't cover in this article. Recursion was much more difficult with SQL 2000.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

sara karasik (4/29/2008)
Thanks for the info on CTE's.
Has anyone compared the speed of CTE's vs. temporary tables vs. a table variable ?

Generally over small datasets a CTE will be faster.

Can a CTE do more than a temp table or a table variable?

I would not say a CTE can do more, it does tend to make code more readable when compared to Derived Tables and inline sql, and it does handle recursion for you. Temp Tables can be indexed and have CRUD operations done on them as can table variables (the CRUD).

Or are CTE's just a cosmetic addition?

I would not call them a "cosmetic" addition just because of their use in recursion and hierarchies. I would use them anywhere I normally use a derived table for readability which would be cosmetic, but code that is easier to read is also easier to tune and debug, so I think CTE's are more than cosmetic even in these cases.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

Lisa... Thanks for the feedback a couple of posts back. 🙂

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

Tech_Newbie SSC Veteran Points: 245 More actions · Answer 6

help please.

I have the following situation:

I want to populate two related tables

Person (PersonID, PersonName, PersonLName)

PersonAddress(PersonAddressID, PersonID, PersonAddr, PersonCity, PersonState, PersonZip, County)

from a table which contains all the data needed

MasterList(PersonName, PersonLName, PersonAddr, PersonCity, PersonState, Personzip, County)

The thing is I am not doing this sequentially, I want to select every 7th person starting from the person in the MasterList table at position 3.

For example:

I have 100 people in the master list

Lets say that I want 30 people to be put into the Persons table and into thier associated address tables.

I want to be able to increment through the MasterList table starting at the person from position 3, and insert every 7th person thereafter into the tables.

If I get to the end of the table and I have not reached my 30 people, I need to loop back around starting at a new position - 2 and then repeat selecting every 7th person from then on out.

Important things:

1. PersonID is an identity, and an auto - incremented value, but within this loop, when I create my entry for Person, I want to turn around and create an entry for PersonAddress, using the newly created PersonID identity value as a foreign key inside of PersonAddress.

How should I write a cte to be able to acomplish this ? Thanks for any assistance provided

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

Like I said on the other article discussion, this isn't the right place and you haven't told us what version of SQL Server you are using... take a look at the article in my signature, and post under the correct forum.

Just a hint... lot's of us monitor all forums... you only need to post once. People tend to get pretty crazy on people that waste time posting on multiple forums... disperses the effort and the answers. Just post once... in the correct forum.

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

kgayda SSCrazy Points: 2157 More actions · Answer 8

This is tres cool! Thanks for posting.

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

hariraj_r Valued Member Points: 57 More actions · Answer 9

Excellant Post.. Thanks

Do we have any functionality in SQL 2000 to acheive the same. I have a similar problem to address in the organisation I work for other than upgrading the DB to 2005.

I have created a cursor to number the records.

David McKinney SSChampion Points: 10472 More actions · Answer 10

hariraj_r (1/23/2009)
Excellant Post.. Thanks
Do we have any functionality in SQL 2000 to acheive the same. I have a similar problem to address in the organisation I work for other than upgrading the DB to 2005.
I have created a cursor to number the records.

Take a look through the previous pages of the discussion. Quite a few posts (notably Jeff Moden's) have talked about this.

Glad you enjoyed it.

David.

Mr. Phantomblot SSC Veteran Points: 205 More actions · Answer 11

If just ms would implement the lead() and lag() analytical functions, this stuff would be straight forward.(http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254388)

Other analytical functions like first_value and last_value would also be much appreciated.

When working with calculations based on big datasets in Oracle, most of my calculations are done in sql, not pl/sql, because the windowing and analytical functions make this possible. To perform the same in MSSqlserver, it requires much more work and the code quickly gets complex and harder to maintain.

The relatively straight forward statement "For each product, i want to know the current price (related to now) and the previous price, if any)" ends up in a bit off a mess of a query in Sqlserver.

If first_value and lag() was implemented in Sqlserver, it would be about as simple as this:

select

p.name,

first_value(p.price) over (partition by p.name order by valid_from_date desc) current_price,

lag(p.price) over (partition by p.name order by valid_from_date desc) previous_price

from products

where valid_from_date <= getdate()

Arshad Kalam SSC Enthusiast Points: 109 More actions · Answer 12

A very nice article. Because sometimes i have to use Row number and i didn't know how to Generate Rows. previously i use to left joins and complex sub-queries to generate row number but this article helps me a lot. Thanks

David McKinney SSChampion Points: 10472 More actions · Answer 13

stianl72 (1/23/2009)
If just ms would implement the lead() and lag() analytical functions, this stuff would be straight forward.(http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254388)

Thanks for that info....very interesting. I just added my vote, and would call on others to do the same. Maybe we'll get something in the next version of SQL Server!

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

Bill Coale (3/13/2008)
For this date range technique, for the end date:
nextrow.PriceStartDate AS EndDate
I usually do:
dateadd(day, -1, nextrow.PriceStartDate) AS EndDate
Also truncate to midnight the start/end date and any date comparisons to the range

I handle the end date solution a little differently. Rather than rely on all other queries to compare by truncating to midnight I force the enddate to the latest possible time on that day.

cast(convert(varchar, next.startdate, 110) + ' 23:59:59.997' as datetime) as enddate

This way you can just do date comparison between from any application. This puts the effort on the architect and not on the developer. It also avoids the pitfalls of "Oh I didn't know I was supposed to do that when checking date ranges". I'm sure there are some issue with doing it this way but it works well for us. :satisfied:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

F. Dwarf SSCommitted Points: 1598 More actions · Answer 15

Déjà vu...I did add this article to my briefcase last year...

Anyway...nice article, David.