You may also use an OLAP ranking function:
With x as (Select Row_Number() Over(Partition By OrderNum Order By OrdSeq
Desc) NewSeq, a.*
From yourTable a)
Select *
From x
Where NewSeq <= 2
Order By OrderNum, NewSeq Desc;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Michael Ryan
Gesendet: Tuesday, 17.7 2012 17:07
An: Midrange Systems Technical Discussion
Betreff: 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!
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
