T-SQL to determine "out of sequence" records

Question

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

I'll explain through 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 produce a list of OrderNumbers that were sent "out of order". So 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 again status 24 (this doesn't count as "out of sequence")).

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

Any idea on how I can accomplish this?

I added the possibility of an order to send out the same status twice in a row (this shouldn't count as "out of sequence")

Thanks in advance.

Answer 1

In SQL Server 2008 onwards...

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

EDIT : Oops, I forgot the WHERE clause, should work now ;)