Question 211213 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL list records where coherent growths go wrong

I have a MySQL table with two columns, row1 and row2, and theese columns together increased.

+------+------+
| row1 | row2 |
+------+------+
|   10 |  100 |
|   11 |  110 |
|   20 |  200 |
|   21 |  210 |
+------+------+

But sometimes an error occured, wrong values inserted:

+------+------+
| row1 | row2 |
+------+------+
|   10 |  100 |
|   11 |  110 |
|   20 |  105 |
|   21 |  210 |
+------+------+

The row1 = 20, row2 = 105 is "wrong", because the previous row1 is 11, and the corresponding row2 is 110, which is larger than 105.

How can I list theese "wrong" rows?

Tags:

2

Answers


  1. 0

    On MySQL 8+, we can use the LAG() window function to detect breaks in the row2 sequence as ordered by row1:

    SELECT row1, row2,
       CASE WHEN LAG(row2) OVER (ORDER BY row1) > row2 THEN 'ERROR' ELSE END AS flag
FROM yourTable
ORDER BY row1;

    The computed flag column generated by the above query would contain ERROR for any record which is breaking sequence.

    Login or Signup to reply.
  2. 0

    Use a self-join query to compare each row with its previous row and check if the value in the rrow2 would be less than the value of the row2 from the previous row,

    SELECT t1.row1, t1.row2
FROM your_table t1
JOIN your_table t2 ON t1.row1 = t2.row1 + 1
WHERE t1.row2 < t2.row2

    on the above query, the table gets joined with itself by using column row1 as a join condition, and the t1 table would represent the current row, whereas the t2 represents the previous row. By using WHERE we filter the results and we get only the rows where the value of rrow2 of the current row is less than row2 of the previous row values,

    +------+------+
| row1 | row2 |
+------+------+
|   20 |  105 |
+------+------+

    I hope it helps!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search