sql - test records are inserted in order

I have a table with an skey co开发者_开发知识库lumn (generated by a sequence number) and a timestamp column.

I need to write a query that will find any records with a higher skey but an older timestamp than preceding records as it will indicate an issue.

ie something along the lines of:

select * from table
where timestamp < (select max(timestamp) from table where skey less than currrecord)
order by skey desc;

Use a self join i.e. join the table to itself with the criteria that you have mentioned (SKey and TimeStamp)

Select T1.Skey HigherSkey, T2.SKey LowerSkey, T1.Timestamp LowerTimestamp, T2.Timestamp HigherTimestamp
From MyTable T1
Inner Join MyTable T2
    On T1.Timestamp < T2.Timestamp
    And T1.Skey > T2.Skey

If your sequence is complete (meaning there are no holes in it) something like this should work:

select t2.* from table t1
inner join table t2 on t2.skey = t1.skey + 1
where t2.timestamp < t1.timestamp
order by t2.skey desc;

If not, you can use something like this to get the ids:

select t2.skey from table t1, table t2
where t1.skey < t2.skey and t2.timestamp < t1.timestamp
group by t2.skey