Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linking to the Previous Row


Linking to the Previous Row

Author
Message
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
jordonpilling (3/13/2008)
a very nice article indeed, however i recently had the same issue on SQL Server 8.0, Is their a related atricle/method for SQL 8?


You should be able to do the same thing by making the PriceCompare a temporary table with an identity column instead of the ROW_NUMBER() for rownum.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Seth-284448
Seth-284448
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 74
I've had to link on a previous row in the past. I've just used self joins, i.e. joining a table on itself. In the join clause, I just put my criteria. For example:

select t1.price as 'oldprice', t2.price as 'newprice' from table1 t1 join table1 t2 on t1.colnum +1= t2.colnum

Is there anything wrong with doing that? Is it less efficient or buggy?

Thanks,

Seth
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11034 Visits: 14858
In your example you are assuming that the PK field is incrementing by 1 and when you have data like in the example that cannot be guaranteed. The CTE version in the article is simple, easy to read and understand, and adaptable. I have used similar methods to your self join, but they really involve joining to derived tables. The CTE is cleaner and easier to read and, I think, should be the preferred way to handle it in SQL 05 and later.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
Seth (3/13/2008)
I've had to link on a previous row in the past. I've just used self joins, i.e. joining a table on itself. In the join clause, I just put my criteria. For example:

select t1.price as 'oldprice', t2.price as 'newprice' from table1 t1 join table1 t2 on t1.colnum +1= t2.colnum

Is there anything wrong with doing that? Is it less efficient or buggy?

Thanks,

Seth


That method works just fine so long as you are linking to the immediately prior ID. If, for example, you want the prior order for the same customer, your ID number is not so easily derived.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
CREATE TABLE #PriceCompare (
ITEMNMBR varchar(31),
ITEMDESC varchar(101),
DOCDATE datetime,
UNITCOST numeric(19, 5),
rownum int identity
)
INSERT INTO #PriceCompare
SELECT
RTRIM(PH.ITEMNMBR) [ITEMNMBR],
RTRIM(i.ITEMDESC) [ITEMDESC],
ph.DOCDATE,
ph.UNITCOST
FROM IV00101 i
INNER JOIN IV30300 ph
ON i.ITEMNMBR = ph.ITEMNMBR
ORDER BY i.ITEMNMBR, ph.DOCDATE

SELECT
currow.ITEMDESC,
ISNULL(prevrow.UNITCOST, 0.00) AS OldCost,
currow.UNITCOST AS RangeCost,
CONVERT(varchar(10), currow.DOCDATE, 110) AS StartDate,
ISNULL(CONVERT(varchar(10), nextrow.DOCDATE, 110), 'CURRENT') AS EndDate
FROM #PriceCompare currow
LEFT JOIN #PriceCompare nextrow
ON currow.rownum = nextrow.rownum - 1
AND currow.ITEMNMBR = nextrow.ITEMNMBR
LEFT JOIN #PriceCompare prevrow
ON currow.rownum = prevrow.rownum + 1
AND currow.ITEMNMBR = prevrow.ITEMNMBR


DROP TABLE #PriceCompare

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Mike DiRenzo
Mike DiRenzo
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 210
Great article. Thanks.
-Mike
MattieNH
MattieNH
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2955 Visits: 901
I recently had this question, and because I'm using SS2K, couldn't use CTEs. Thanks to this terrific forum, I was provided with a very nice solution that doesn't rely on CTEs or row numbers. Check out this link

http://www.sqlservercentral.com/Forums/Topic462350-373-1.aspx#bm462366


Mattie



knechod
knechod
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 268
Is it strictly necessary to use the OVER/Partition clause in the CTE? Isn't it solely to restart the row numbering? Or what am I missing?

--
Please upgrade to .sig 2.0
Calvin Lawson
Calvin Lawson
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 102
Nice article...very interesting solution! I like the RowNumber with partition functionality, seems useful for a number of things.

My only complaint is that the SQL Code is very difficult to read in those little itty boxes.

Signature is NULL
Manie Verster
Manie Verster
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1310 Visits: 986
Hi David,

TongueTongue Thanks for a very nice article. I am still a bit new on SQL 2005 so I had a bit of difficulty to understand the code. I come from a MSSql 7 background and must say I have learned a lot from this forum. I immediately tested your code and after battling just a bit I got it right. In the past I solely used views for the purpose of quickly listing some data but this way is very nice.

Thanks a span.

Confusius said: "Ask a question and be a foll for a moment, keep quiet and remain a fool forever"

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search