Solved

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

Posted on 2007-03-23
3
431 Views
Last Modified: 2008-01-09
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)
0
Comment
Question by:NeoTeq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18778608
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

0
 
LVL 2

Expert Comment

by:sunil_mails
ID: 18779986
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
0
 
LVL 10

Author Comment

by:NeoTeq
ID: 18780311
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question