CODEWITHSUNDEEP
sqlsql-server
haxx
haxx

Reputation: 15

Verify if two columns aren't sort in asc in SQL

I am trying to find out a result of a query but didn't manage to do it, i have four columns:

ID   Nr  Date       Payment
100  1   10012020   99
100  2   10022020   87
100  3   10032020   75

The 'Nr' column and 'Date' column are linked meaning that the value 1 from 'Nr' column has to match with the lowest date from the 'Date' column, value 2 with the second lowest date and so on.

The problem is that i have some ID's which values from 'Nr' column and 'Date' column doesn't match, meaning i have something like this:

 ID   Nr  Date       Payment
 200  3   10012020   99
 200  1   10022020   87
 200  2   10032020   75

How can i find the ID's that has the nonmatching between 'Nr' column and 'Date' column ?

Thank you.

Upvotes: 0

Views: 53

Answers (1)

forpas
forpas

Reputation: 164164

If your Date's column data type is Date you can use LEAD() and LAG() window functions:

select t.*
from (
  select *,
    lag(date) over (order by Nr) previous_date,
    lead(date) over (order by Nr) next_date
  from tablename
) t
where t.date < t.previous_date or t.date > t.next_date

It is not clear from your sample data if the ordering that you want to apply should affect the whole table or just each ID.
If it is the latter case then the over clauses should include partition:

select t.*
from (
  select *,
    lag(date) over (partition by ID order by Nr) previous_date,
    lead(date) over (partition by ID order by Nr) next_date
  from tablename
) t
where t.date < t.previous_date or t.date > t.next_date

Upvotes: 2

Related Questions