SQL - Select records that their columns do not follow the same order

user_10698190 0
Viewed 61

Given we have following table where the series number and the the date should increment

+----+--------+------------+
| id | series |    date    |
+----+--------+------------+
|  1 |     10 | 2020-08-13 |
|  2 |      9 | 2020-08-02 |
|  3 |      8 | 2020-06-23 |
|  4 |      7 | 2020-06-08 |
|  5 |      6 | 2020-05-20 |
|  6 |      5 | 2020-05-05 |
|  7 |      4 | 2020-05-01 |
+----+--------+------------+

Is there a way to check if there are records that do not follow this pattern ? For example row 2 has bigger series number but it's date is before row 3

+----+--------+------------+
| id | series |    date    |
+----+--------+------------+
|  1 |     10 | 2020-08-13 |
|  2 |      9 | 2020-06-02 |
|  3 |      8 | 2020-07-23 |
|  4 |      7 | 2020-06-08 |
|  5 |      6 | 2020-05-20 |
|  6 |      5 | 2020-05-05 |
|  7 |      4 | 2020-05-01 |
+----+--------+------------+
mariadb gaps-and-islands window-functions mysql sql
3 Answers

You can use window functions:

select *
from (
    select t.*, lead(date) over(order by series) lead_date
    from mytable t
) t
where date > lead_date

Alternatively:

select *
from (
    select t.*, lead(series) over(order by date) lead_series
    from mytable t
) t
where series > lead_series
user_106767160

You can use lag():

select t.*
from (select t.*,
             lag(id) over (order by series) as prev_id_series,
             lag(id) over (order by date) as prev_id_date
      from t
     ) t
where prev_id_series <> prev_id_date;
user_11440350

You can fetch problematic rows and their corresponding conflicting rows using SELF JOIN like this (assuming your table is called "series"):

SELECT s1.id AS row_id, s1.series AS row_series, s1.date AS row_date, 
       s2.id AS conflict_id, s2.series AS conflict_series, s2.date AS conflict_date
FROM series AS s1
JOIN series AS s2
  ON s1.series > s2.series AND s1.date < s2.date;
user_136012210
Related