I am using a query that I found on Stack Overflow to return a row in my table when a production counter resets at the start of a shift. However, it is also returning rows when the production counter changes from hundreds to thousands, thousands to ten thousands, etc.
So, if my counter values are as follows:
| ndx | counter |
|---|---|
| 1 | 700000 |
| 2 | 252 |
| 3 | 2178 |
| 4 | 4101 |
| 5 | 6027 |
| 6 | 7953 |
| 7 | 9876 |
| 8 | 11769 |
| 9 | 13695 |
| 10 | 15618 |
The query that I used is:
SELECT results.* FROM table AS results WHERE
results.counter < (
SELECT prevs.counter FROM table as prevs WHERE
(prevs.ndx = results.ndx - 1)
)
I’m expecting to get back:
| ndx | counter |
|---|---|
| 2 | 252 |
But the query returns the following:
| ndx | counter |
|---|---|
| 2 | 252 |
| 3 | 2178 |
| 8 | 11769 |
ColinM is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
The issue is coming because your query checks if the current row’s counter is less than the previous row’s counter.
And this condition is satisfied not only when the counter resets but also when there are smaller counter increments (like due to rolling over from hundreds to thousands etc).
To identify only rows where the counter “resets”, you need to add an additional condition that defines a “reset.”
This amendments should work –
SELECT results.*
FROM table AS results
WHERE
results.counter < (
SELECT prevs.counter
FROM table AS prevs
WHERE prevs.ndx = results.ndx - 1
)
AND results.counter < 1000;