self joined tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

self joined tables

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2014-05-06 : 17:57:28

In order to link two succeeding records (ordered by time), I did the following nested query:

select time as end, (select TOP 1 time 
	             from table as tableStart 
	             where tableStart.time < table.time
	             order by time DESC) as start
from table

This works fine, but as I need to get more columns from that nested query I am looking for a self joined tables solution. Something like:

select table.time as end, tableStart.time as start
from table
     left outer join TOP 1 table as tableStart on tableStart.time < table.time order by tableStart.time DESC

off cource that doesn't work... but is such Join possible?

cheers,
Martin

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-07 : 11:04:41

quote:
Originally posted by barnabeck

In order to link two succeeding records (ordered by time), I did the following nested query:
select time as end, (select TOP 1 time
from table as tableStart
where tableStart.time < table.time
order by time DESC) as start
from table
This works fine, but as I need to get more columns from that nested query I am looking for a self joined tables solution. Something like:
select table.time as end, tableStart.time as start
from table
left outer join TOP 1 table as tableStart on tableStart.time < table.time order by tableStart.time DESC

off cource that doesn't work... but is such Join possible?

cheers,
Martin

It's a little hard to tell from your brief description, but the Row_Number() function is handy for such things.

e.g.


select time,rn=Rownumber() over(order by time) 
from table

will produce two columns. You could use the rn column in a self join to find the successive rows. rg. rn=1 would be the earliest, rn=2 would be the next later one, etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-05-09 : 05:20:22

as i understand this is what you want


select t1.time as end,t2.time as start,t2.column2,t2.column3 ,...
from table t1
OUTER APPLY (select TOP 1 time, column2,column3 ... 
	             from table as tableStart 
	             where tableStart.time < table.time
	             order by time DESC) t2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

barnabeck
Posting Yak Master

236 Posts

Posted - 2014-05-13 : 05:02:02

Sorry for my late feedback on your proposals... I'm working on too many issues at the same time and the email alert doesn't seem to function in my case. Anyway...

Thank you gbritton, visakh16 solution is what I was looking for. But as the execution performance is very poor (5 Minutes!!!) I guess I go back to my first aproach... or even try using the rownumber function.
Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources