Sorry, but I do not see what makes that an anomaly. (I would have the status column an integer datatype instead of text, anyway …) All rows have different id and datetime values. I understand that “19” is in two rows and both have the same “status” value. But “18” also has two rows with “available” status values.
When - ordered by “hour” [datetime] and / or “ID”
and - grouped by “element”
sequential values for “status” should alternate between the two possible values (see how “status” sounds like a boolean?)
You want a query to find elements where this is not the case. i.e. the corrupt elements?
It is safe to say that what you would initially like to do is to identify which “element” values have more than one unmatched value? i.e. in your example, the result might be
What he’s saying is that every state-change of an element should be recorded, and that row ID’s 1 and 2 in his OP represent an anomaly - the system recorded a state change to Available when the state was last recorded as changing to Available.
This… shouldnt be too difficult… (Code untested, off the top of my head, and i’m sure there’s a better way.)
SELECT a.element,a.id,a.status,
(SELECT b.id FROM statustable b WHERE a.element = b.element AND b.hour < a.hour ORDER BY hour DESC LIMIT 1) as last_id,
(SELECT b.status FROM statustable b WHERE a.element = b.element AND b.hour < a.hour ORDER BY hour DESC LIMIT 1) as last_status
FROM statustable a
WHERE a.status = a.last_status
ORDER BY a.hour DESC
[Err] 1054 - Unknown column ‘a.last_status’ in ‘where clause’[quote=“m_hutley, post:8, topic:346853, full:true”]
This… shouldnt be too difficult… (Code untested, off the top of my head, and i’m sure there’s a better way.)
SELECT a.element,a.id,a.status,
(SELECT b.id FROM statustable b WHERE a.element = b.element AND b.hour < a.hour ORDER BY hour DESC LIMIT 1) as last_id,
(SELECT b.status FROM statustable b WHERE a.element = b.element AND b.hour < a.hour ORDER BY hour DESC LIMIT 1) as last_status
FROM statustable a
WHERE a.status = a.last_status
ORDER BY a.hour DESC
I lean towards avoiding terseness whenever possible if there’s a chance it may compromise clarity. I chose those names in hope of suggesting the values involved the aggregate COUNT function https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html
The “avail” and “notavail” names were in hope of suggesting those two values of the status field. So yes, they might better be named more explicitly as “count_status_available” and “count_status_unavailable”.
EDIT
Indeed, there are many potential “gotchas”. This is why my previous “initially”. My thought was a quick check for a problem would be a good start, under the assumption that concern of the “gotchas” involving a limited data set (eg. element = #) would be next.
More detailed specs regarding exactly what kind of results are wanted would be a big help at arriving at a better solution sooner.
that is the correct approach – stop erroneous data from being inserted into the database in the first place
even if you identify the existing bad rows now, with the queries people are suggesting for you, you must still do something to prevent this from happening again
Thank you for suggestion.
I’m sorry but unfortunately this table is compiled by a hosted service.
Otherwise I would have already solved.
On this table I can only make a SELECT query, I don’t have other privileges