Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Linking to the Previous Row Expand / Collapse
Author
Message
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
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
Posted Thursday, March 13, 2008 10:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 10,910, Visits: 12,548
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
Posted Thursday, March 13, 2008 10:42 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
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
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: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
Great article. Thanks.
-Mike
Post #468908
Posted Thursday, March 13, 2008 2:13 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:42 AM
Points: 2,543, Visits: 774
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
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: Friday, March 21, 2014 5:33 PM
Points: 160, Visits: 262
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
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
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: Friday, March 28, 2014 5:06 AM
Points: 1,204, Visits: 915
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

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)
Post #469211
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse