Selecting data from previous record in current record with custom order by


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:

MyKey:     MySortValue:
1              50
2              20
3              70

MyKey:     MySortValue:   MyPreviousKey:     MyPreviousSortValue:
2              20                    NULL                      NULL
1              50                    2                            20
3              70                    1                            50

(I hope it formats okay)
LVL 10
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you should do this in the front-end. much simpler and hence more elegant :)
in sql, this will be a terrible performance results...

anyhow, I would give this a whirl
select t.*, p.*
from MyTable t
left join MyTable p
  on p.MyKey = ( select top 1 i.MyKey from MyTable i where i.MySortValue < t.MySortValue order by i.MySortValue desc )
order by t.MySortValue

heres the answer buddy

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
NeoTeqAuthor Commented:
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.
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.