Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David McKinney » Linking to the Previous Row
Add to briefcase
148 posts, Page 2 of 15
««12345»»»

Linking to the Previous Row Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Tom Garth
Posted Thursday, March 13, 2008 8:42 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #468771
Seth-284448
Posted Thursday, March 13, 2008 10:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 9:04 AM
Points: 10, 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
Post #468879
Jack Corbett
Posted Thursday, March 13, 2008 10:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871
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

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
Post #468883
GSquared
Posted Thursday, March 13, 2008 10:42 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571
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
Post #468890
Tom Garth
Posted Thursday, March 13, 2008 10:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #468899
Mike DiRenzo
Posted Thursday, March 13, 2008 11:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:11 PM
Points: 143, Visits: 183
Great article. Thanks.
-Mike
Post #468908
MattieNH
Posted Thursday, March 13, 2008 2:13 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 2,379, Visits: 708
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


Post #469027
knechod
Posted Thursday, March 13, 2008 3:11 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 6:47 AM
Points: 159, Visits: 256
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
Post #469047
Calvin Lawson
Posted Thursday, March 13, 2008 3:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, 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
Post #469051
Manie Verster
Posted Friday, March 14, 2008 1:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:00 AM
Points: 1,151, Visits: 879
Hi David,

:P:P 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"

Manie Verster
Developer
Johannesburg
South Africa

Life is about choices.... I choose to be happy today
Post #469211
« Prev Topic | Next Topic »

Add to briefcase
148 posts, Page 2 of 15
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.