How to check if SQL records are in a specific order

Question

I'm having trouble figuring out how I can check if records on a table are in a specific order. The simplified table design is essentially this:

+------------+----------------+--------+
| ID (GUID)  |   StartDate    | NumCol |
+------------+----------------+--------+
| CEE8C17... | 8/17/2019 3:11 |     22 |
| BB22001... | 8/17/2019 3:33 |     21 |
| 4D40B12... | 8/17/2019 3:47 |     21 |
| 3655125... | 8/17/2019 4:06 |     20 |
| 3456CD1... | 8/17/2019 4:22 |     20 |
| 38BAF92... | 8/17/2019 4:40 |     19 |
| E60CBE8... | 8/17/2019 5:09 |     19 |
| 5F2756B... | 8/17/2019 5:24 |     18 |
+------------+----------------+--------+

The ID column is a non-sequential GUID. The table is sorted by default on the StartDate when data is entered. However I am trying to flag instances where the NumCol values are out of descending order. The NumCol values can be identical on adjacent records, but ultimately they must be descending.

+--------+
| NumCol |
+--------+
|     22 |
|    *20 | <- I want the ID where this occurs
|     21 |
|     20 |
|     20 |
|     19 |
|     19 |
|     18 |
+--------+

I've tried LEFT JOIN this table to itself, but can't seem to come up with an ON clause that gives the right results:

ON a.ID <> b.ID AND a.NumCol > b.NumCol

I also thought I could use OFFSET n ROWS to compare the default sorted table against one with an ORDER BY NumCol performed on it. I can't come up with anything that works.

I need a solution that will work for both SQL Server and SQL Compact.

Answer 1

With EXISTS:

select t.* from tablename t
where exists (
  select 1 from tablename
  where numcol > t.numcol and startdate > t.startdate
)

Or with row_number() window function:

select t.id, t.startdate, t.numcol
from (
  select *,
    row_number() over (order by startdate desc) rn1,
    row_number() over (order by numcol) rn2
  from tablename 
) t
where rn1 > rn2

See the demo.