Joining a table onto itself

[NCOELHO]

Hello,
I have been having a hard time with this issue. I am attempting to join a table onto itself to get the closest date onto a single row.
What i mean is:
I have the following data
id date
1 10/07/08
2 10/06/07
3 10/06/03
4 10/06/03

the new table should have the current id and the one closes to it as so.
1 10/07/08 2 10/06/07
2 10/06/07 3 10/06/03
3 10/06/03 null null
4 10/06/03 null null
but i am getting duplicates do to the 10/06/03.
1 10/07/08 2 10/06/07
2 10/06/07 3 10/06/03
2 10/06/07 4 10/06/03
3 10/06/03 null null
4 10/06/03 null null
i want so that if there is a duplicate i can take the id thats higher. I cant figure it out.
This is my current sql:

SELECT PB.ID,PB.StartDate, PB2.ID, PB2.Startdate
from table PB
left outer join table PB2 on PB.keyID = PB2.keyID
and PB2.StartDate < PB.StartDate
and PB.StartDate = (select top(1) StartDate from table PB3 where PB.keyID = PB3.keyID
and PB2.StartDate < PB3.StartDate order by PB3.StartDate asc)


Thanks for the help.

 

[Qik3Coder]

That was harder than i thought...

create table #tst1
(id int identity(1,1), dte datetime)
insert into #tst1 (dte) Values('10/07/08')
insert into #tst1 (dte) Values('10/06/07')
insert into #tst1 (dte) Values('10/06/03')
insert into #tst1 (dte) Values('10/06/03')

select t1.id, t1.dte, t2.id, t2.dte
from #tst1 t1
Left join #tst1 t2 on
	t1.dte > t2.dte
	and t2.id = (
		Select top 1 t3.id 
		from #tst1 t3 
		where t3.dte = t2.dte 
		order by t3.dte desc, t3.id desc)
	and t2.dte = (
		select max(t4.dte) 
		from #tst1 t4 
		where t4.dte < t1.dte)
order by t1.id, t1.dte

-Sometimes the answer to your question is the hack that works

 

[fredericofonseca]

or, at least in SQL 2005 (and other database engines)

select t1.id, t1.dte,(
        Select top 1 t3.id
        from #tst1 t3
        where t3.dte < t1.dte
        order by t3.dte desc, t3.id desc) as id2,
        (select max(t4.dte)
        from #tst1 t4
        where t4.dte < t1.dte) as dte2
from #tst1 t1
order by t1.id, t1.dte

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[NCOELHO]

Thanks guys that worked!