SQL Server : verify that two columns are in same sort order

Question

I have a table with an ID and a date column. It's possible (likely) that when a new record is created, it gets the next larger ID and the current datetime. So if I were to sort by date or I were to sort by ID, the resulting data set would be in the same order.

How do I write a SQL query to verify this?

It's also possible that an older record is modified and the date is updated. In that case, the records would not be in the same sort order. I don't think this happens.

I'm trying to move the data to another location, and if I know that there are no modified records, that makes it a lot simpler.

I'm pretty sure I only need to query those two columns: ID, RecordDate. Other links indicate I should be able to use LAG, but I'm getting an error that it isn't a built-in function name.

In other words, both https://dba.stackexchange.com/questions/42985/running-total-to-the-previous-row and Is there a way to access the "previous row" value in a SELECT statement? should help, but I'm still not able to make that work for what I want.

`LAG` certainly exists in 2014. Please check your version with `SELECT @@VERSION`. Also should any tied dates violate the rule as they are not strictly ascending with Id? — Martin Smith, Dec 20 '18 at 18:39
A simple way to verify this is to create a column with the ROW_NUMBER() OVER the Date column. That way you could compare it with the ID column and see if any are different (updated). hope it helps — Kim Lage, Dec 20 '18 at 18:44
@MartinSmith - Oh, duh. I'm using 2014 management, but the underlying database is only 2008 R2. Ok, that explains why the lag doesn't work. Thank you. — thursdaysgeek, Dec 20 '18 at 18:46

score 3 · Answer 1 · answered Dec 20 '18 at 19:33

One method uses window functions:

select count(*)
from (select t.*,
             row_number() over (order by id) as seqnum_id,
             row_number() over (order by date, id) as seqnum_date
      from t
     ) t
where seqnum_id <> seqnum_date;

When the count is zero, then the two columns have the same ordering. Note that the second order by includes id. Two rows could have the same date. This makes the sort stable, so the comparison is valid even when date has duplicates.

forpas · Answer 2 · 2018-12-20T18:55:57.010

Use this:

SELECT ID, RecordDate FROM tablename t
WHERE 
  (SELECT COUNT(*) FROM tablename WHERE tablename.ID < t.ID) 
  <> 
  (SELECT COUNT(*) FROM tablename WHERE tablename.RecordDate < t.RecordDate);

It counts for each row, how many rows have id less than the row's id and
how many rows have RecordDate less than the row's RecordDate.
If these counters are not equal then it outputs this row.
The result is all the rows that would not be in the same position after sorting by ID and RecordDate

score 2 · Answer 3 · answered Dec 20 '18 at 18:47

If you cannot use window functions, you can use a correlated subquery and EXISTS.

SELECT *
       FROM elbat t1
       WHERE EXISTS (SELECT *
                            FROM elbat t2
                            WHERE t2.id < t1.id
                                  AND t2.recorddate > t1.recorddate);

It'll select all records where another record with a lower ID and a greater timestamp exists. If the result is empty you know that no such record exists and the data is like you want it to be.

Maybe you want to restrict it a bit more by using t2.recorddate >= t1.recorddate instead of t2.recorddate > t1.recorddate. I'm not sure how you want it.

score 1 · Answer 4 · answered Dec 20 '18 at 19:13

1

the above solutions are all good but if both dates and ids are in increment then this should also work

select  modifiedid=t2.id from 
yourtable t1 join yourtable t2
on t1.id=t2.id+1 and t1.recordDate<t2.recordDate

answered Dec 20 '18 at 19:13

DhruvJoshi

17,041
6
41
60

I'm not sure what the modifiedid part is - I just selected t2.id. And found something. The difference is only milliseconds, but it does happen. I'm going to try a truncated date - if it happens on the same day, that's probably ok. – thursdaysgeek Dec 20 '18 at 19:21
This query is significantly faster than the other two. It gave me results in a few minutes, and I've killed the other two after 15+. All of the differences were a lot less than an hour, and I think that gives me the information I need. – thursdaysgeek Dec 20 '18 at 19:54
@thursdaysgeek - This query assumes the ids will be sequential (without gaps) which may well not be valid – Martin Smith Dec 20 '18 at 20:15
@MartinSmith I did find gaps, so perhaps it gave me only a partial set. – thursdaysgeek Dec 20 '18 at 21:13

SQL Server : verify that two columns are in same sort order

4 Answers4