T-SQL for defining out-of-sequence records

Question

I am using a Microsoft SQL server and I need to identify records that are "out of sequence" from the table.

I will explain an example. I have the following table structure:

OrderNumber OrderStatus EventDateTime
0001522989  22          2014-04-14 05:49:25.4414243
0001522989  26          2014-04-14 05:51:16.7047485
0001522989  23          2014-04-14 05:51:17.8602798
0001522990  23          2014-04-14 05:51:19.9603575
0001522990  24          2014-04-14 05:52:06.5803494
0001522990  24          2014-04-14 05:52:06.5803494

Now I need to create a list of OrderNumbers that were sent out of order. Thus, in this example, the list will contain only one value: "0001522989".

Order 0001522990 was sent in the correct sequence (first status 23, then status 24, and then status 24 again (this does not count as “out of sequence”)).

Order 0001522989 was not sent in the correct sequence (first state 22, then status 26, and then state 23).

Any idea on how I can do this?

Thanks in advance.

EDIT ( " " )

Answer 1

SQL Server 2008 ...

SELECT
  OrderNumber
FROM
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY OrderNumber ORDER BY OrderStatus, EventDateTime  )   AS sequenceCorrect,
    ROW_NUMBER() OVER (PARTITION BY OrderNumber ORDER BY              EventDateTime)   AS sequenceActual
  FROM
    yourTable
)
  AS yourTableSequenced
WHERE
  sequenceCorrect <> sequenceActual
GROUP BY
  OrderNumber
ORDER BY
  OrderNumber

: , WHERE, ;)

Answer 2

:

,

:

select OrderNumber 
from Orders o1
where exists (select * from Orders o2 
              where o2.OrderNumber = o1.OrderNumber
                and o2.OrderStatus >= o1.OrderStatus 
                and o2.EventDateTime < o1.EventDateTime)