Sql Query First, Next, Last Rows in a Set

Question

I have two tables that I am trying to join together multiple times. The Parent table is called Jobs, and the child table is called Routings. A Job can have 1 or more Routings. I need my output to contain one record for each job, joined with three separate joins to the Routings table. One join is for Current data (the first null date value in the sequence), one for next (the sequence coming immediately after the current) and the final for the last (defined as the highest sequential number for the job).

Below is a small sample I have put together to provide sample data and the desired output. It takes the problem to a simpler form, showing really only the Routings 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 have attempted this query through many joins, but it seems to be omitting results when no next routing exists (I need null values). Performing a Left Outer Join didn't remedy this. I'm not sure if this is because it's 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

Wouldn't it make sense, to store those values as columns in your jobs table? Then just update them when (I assume) your workflow calls for it? Then you could just do 2 inner joins Jobs->Routes on previous step and Jobs->Routes on next step.

Answer 2

Here's one solution

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

Answer 3

I have attempted this query through many joins, but it seems to be omitting results when no next routing exists (I need null values). Performing a Left Outer Join didn't remedy this.

Make sure you put any filters against the outer joined tables in the JOIN clause, not the WHERE clause. eg:

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

Rather than:

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

The second version is equivalent to an inner join.