NeoTeq
asked on
Selecting data from previous record in current record with custom order by
Experts,
I have a bit of a challenge for you. Mind you, I did already solve it but my solution is quite ugly (using multiple subqueries etc). I am looking for the most elegant and efficient way to select data from a "previous" record in the "current" record in a query. To explain what I mean, here's the example of the data and the example of what I want the results to be:
MyTable:
MyKey: MySortValue:
1 50
2 20
3 70
MyResultView:
MyKey: MySortValue: MyPreviousKey: MyPreviousSortValue:
2 20 NULL NULL
1 50 2 20
3 70 1 50
(I hope it formats okay)
I have a bit of a challenge for you. Mind you, I did already solve it but my solution is quite ugly (using multiple subqueries etc). I am looking for the most elegant and efficient way to select data from a "previous" record in the "current" record in a query. To explain what I mean, here's the example of the data and the example of what I want the results to be:
MyTable:
MyKey: MySortValue:
1 50
2 20
3 70
MyResultView:
MyKey: MySortValue: MyPreviousKey: MyPreviousSortValue:
2 20 NULL NULL
1 50 2 20
3 70 1 50
(I hope it formats okay)
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
AngelIII, your solution was definately cleaner than mine and it worked, thanks. Don't worry about the speed implications as this happens on a small table only (30-100 rows).
Sunil, the sorting has to be on MySortValue, not on my key since there can be all sorts of gaps there.
Sunil, the sorting has to be on MySortValue, not on my key since there can be all sorts of gaps there.
select x.MyKey, x.MySortValue, Y.MyKey as MyPreviousKey, x.MySortValue as MyPreviousSortValue
from MyTable X
left join MyTable Y
on a.MyKey=b.MyKey-1