Reference Previous Row

Hi

I am attempting to reference the previous row in a non contiguous record set.

Recordset:

ID      Vehicle      Millage      
102      ABC123      10234
88      ABC123      99950
55      ABC123      99723

What I want to get from my query is:

ID      Vehicle      Millage       PMillage      
102      ABC123      10234             99950
88      ABC123      99950            99723
55      ABC123      99723            NULL

I have so far;

SELECT     c.ID, c.Volume, c.Vehicle, P.Millage AS PMillage, c.Millage
FROM         FuelOnly AS c LEFT OUTER JOIN
                      FuelOnly AS P ON c.Vehicle = P.Vehicle AND P.ID = c.ID - 1
WHERE     (c.Vehicle = N'C2 GBA')
ORDER BY c.ID DESC

This doe’s not work as the rows are non contiguous. How would I correct the query to return the required result I am using SQL 2008?




LVL 1
JoeBo747Asked:
Who is Participating?
 
deightonprogCommented:
how a bout a sub query to give the field, get the next highest id

SELECT     c.ID, c.Volume, c.Vehicle,
 (SELECT TOP 1 p.millage FROM FuelOnly P WHERE P.ID < C.ID AND P.Vehicle = C.Vehicle ORDER BY P.id DESC) AS PreviousMillage
 , c.Millage
FROM         FuelOnly AS c
WHERE     (c.Vehicle = N'C2 GBA')
ORDER BY c.ID DESC
0
 
nitinsawhneyCommented:
Create a temp table with contiguous ids at the run time and then use that table for your query.

It should look like
temptable:
TID ID      Vehicle      Millage      
 3    102      ABC123      10234
 2    88      ABC123      99950
 1    55      ABC123      99723
0
 
JoeBo747Author Commented:
Hi Thanks for the example, I had looked at using cte but your method provides a much simpler solution. - thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.