RE: SQL - Current and Previous Status

No idea how efficient this is, but it appears to work!

with curr as (select ORDSTAT currstat, ORDSEQ currseq
from ORDERFIL
where ORDNUM=xxxxx
order by ORDSEQ desc
fetch first 1 rows only),
prev as (select ORDSTAT prevstat, ORDSEQ prevseq
from ORDERFIL
where ORDNUM=xxxxx and
ORDSEQ<(select currseq from curr)
order by ORDSEQ desc
fetch first 1 rows only)
select ORDNUM, currstat, ifnull(prevstat,' ')
from ORDERFIL
join curr on 1=1
left outer join prev on 1=1
where ORDNUM=xxxxx and ORDSEQ=currseq

Replace xxxxx with your desired order number.

Is there a better way to get info from a single record table than "join xxx
on 1=1"?

Have fun!

Richard


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Tuesday, July 17, 2012 11:07 AM
To: Midrange Systems Technical Discussion
Subject: SQL - Current and Previous Status

Ok...I have a file (ORDERFIL) that has an order number (ORDNUM), a status
(ORDSTAT), and a sequence number (ORDSEQ). The sequence number is just a one
up number - it's not the row number, and it's not related to an order.
However, it will be in order by status for an order. For a given order
number, I want to get the current status and the previous status. I was
thinking along the lines of a select work order, and then another select
(temporary table?) where I select for that order number and order by
sequence number descending. Does this make sense? Any ideas?

ORDNUM ORDSTAT ORDSEQ
12345 Ship 15
23456 Build 18
12345 Build 13
34567 Process 12
23456 Process 11
12345 Design 05

So I would want to retrieve 12345, Ship, Build; 23456, Build, Process;
34567, Process.

TIA!
--