Sql Query First, Next, Last Rows in Set

Question

I have two tables that I try to combine several times. The parent table is called Jobs, and the child table is called Routing. A task can have 1 or more routes. I need my output to contain one entry for each job, connected to three separate connections in the route table. One connection is for current data (the first value of the zero date in the sequence), one for the next (the sequence arriving immediately after the current) and final for the last (defined as the highest serial number for the job).

Below is a small sample that I collected to provide sample data and the desired result. This refers to a simpler form, showing only the route table and not the Job table. If I can find a way to more easily extract the current, next and last values, I can take it from there.

I tried to execute this query using many connections, but it seems to omit the results when there is no next routing (I need null values). Performing a left external connection did not help. I'm not sure if this is because it is SQL Server 2000 or what.

drop table #routing

create table #routing
(
routingId int not null primary key,
jobid int not null,
sequence int not null,
sentdate datetime
)

insert into #routing
select
1, 1, 1, '1/1/2009'
union
select
2, 1, 2, '1/2/2009'
union
select
3, 1, 3, '1/3/2009'
union
select
4, 1, 4, null
union
select
5, 1, 5, null
union
select
6, 2, 1, '1/1/2009'
union
select
7, 2, 2, '1/2/2009'
union
select
8, 2, 3, '1/3/2009'

select * from #routing


/*
Expected Result:

Two Records, one for each job

JobId,  CurrentRoutingId,   NextRoutingId,  LastRoutingId
1       4                   5               5
2       null                null            8

*/

Answer 1

, , , , ( ). .

, - JOIN, WHERE. :

select curr.jobid, curr.routingid, prev.routingid as prev_routingid
from #routing curr
left join #routing prev 
  on curr.jobid = prev.jobid
 and curr.sequence = prev.sequence+1

select curr.jobid, curr.routingid, prev.routingid as prev_routingid
from #routing curr
left join #routing prev 
  on curr.jobid = prev.jobid
where curr.sequence = prev.sequence+1

.

Answer 2

, ? , ( ) ? 2 Jobs- > Jobs- > .

Answer 3

select r.jobid, min(rn.routingid) as nextroutingid, max(rl.routingid) as lastroutingid,
max(rn.routingid) as currentroutingid
from routing r
    left join routing rn on (rn.jobid = r.jobid) and (rn.sentdate is null)
    left join routing rl on (rl.jobid = r.jobid)
group by r.jobid