Re: SQL - Current and Previous Status

Very sorry.

SELECT now.ordnum, now.ordstat, prev.ordstat as prevstat
FROM orderfil now
LEFT OUTER JOIN orderfil prev
ON prev.ordnum = now.ordnum AND prev.ordseq < now.ordseq
WHERE now.ordseq = (SELECT MAX(ordseq)
FROM orderfil sub
WHERE sub.ordnum = now.ordnum)
AND (prev.ordnum IS NULL
OR prev.ordseq =
(SELECT MAX(ordseq)
FROM orderfil sub
WHERE sub.ordnum = now.ordnum
AND sub.ordseq < now.ordseq))


++
Dennis
++
"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in: we're computer professionals. We cause accidents."
-- Nethaniel Borenstein




Bravely sent from my Galaxy tablet phone.
++


Dennis <iseries@xxxxxxxxxxxx> wrote:

SELECT NOW.ORDNUM, NOW.ORDSTAT, PREV.ORDSTAT AS PREV_STAT
FROM ORDERFIL NOW
LEFT OUTER JOIN ORDERFIL PREV
ON PREV.ORDNUM
AND PREV.ORDSEQ < NOW.ORDSEQ
WHERE NOW.ORDSEQ = (
SELECT MAX(ORDSEQ) from ORDERFIL SUB
WHERE SUB.ORDNUM = NOW.ORDNUM)
AND (PREV.ORDNUM IS NULL
OR PREV.ORDSEQ =
(SELECT MAX(ORDSEQ)
FROM ORDERFIL SUB
WHERE SUB.ORDNUM=NOW.ORDNUM
AND SUB.ORDNUM < NOW.ORDSEQ))


I typed this on my phone, so check for syntax etc.

Something like that should help you get started.
++
Dennis
++
"When I was crossing the border into Canada, they asked if I had any firearms with me. I said, 'Well, what do you need?'"
-- Steven Wright


Bravely sent from my Galaxy tablet phone.
++


Michael Ryan <michaelrtr@xxxxxxxxx> wrote:

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!