Non Sequential Join
-
create table Addr
(
id int primary key,
NAME VARCHAR(20)
)
GO
insert into Addr VALUES (1,'D1')
insert into Addr VALUES (4,'D2')
insert into Addr VALUES (5,'D3')
insert into Addr VALUES (8,'D4')
insert into Addr VALUES (11,'D5')
select * from addr
ID Name
0 D1
4 D2
5 D3
8 D4
11 D5
The Output should be
ID Name other id OtherName
0 D1 4D2
4 D2 5 D3
5 D3 8 D4
8 D4 11 D5
11 D5 NULL NULL
-
Lots of ways of doing this
select a.ID,a.name,b.id as 'other id',b.name as OtherNamefrom addr a
left outer join addr b on b.id>a.id
and not exists (select * from addr c where c.id>a.id and c.id<b.id)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
The CTE's are not necessary, they just made the syntax look nice.
[font="Courier New"]; WITH Data1 (Id, [Name], I)
AS (
SELECT Id, Name, ROW_NUMBER() OVER (ORDER BY Id) FROM addr
) , Data2 (Id, [Name], I)
AS (
SELECT Id, Name, ROW_NUMBER() OVER (ORDER BY Id) FROM addr
)
SELECT
Data1.Id, Data1.[Name]
, Data2.Id, Data2.[Name]
FROM
Data1
LEFT JOIN Data2 ON Data1.I = Data2.I-1[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply