I need some help for a MySQL query.
Let's assume we have a table with the columns "id", "unique_id" and "consecutive_id". The numbers in column "id" are NOT always consecutive, while we have to keep consecutiveness in column "consecutive_id". Basically every row should get its own consecutive number, but sometimes there may occur rows that should share the same consecutive number. Such rows have the same value in column "unique_id". I need a query to find the first ID of a row that has more than one row with the same consecutive ID and a unique ID which is not part of another row.
In created a little fiddle at https://www.db-fiddle.com/f/hy8SACLyM2D65H2ZY31c2f/0 to demonstrate my issue. As you can see, IDs 3 and 5 have the same consecutive number (2). That's okay as they share the same unique ID. IDs 9, 10, 12 and 14 also have the same consecutive number (4), but only IDs 9 and 10 have an identical unique ID. Therefore in this case the query should find ID 12.
Can you please help me with developing a solution for this?
Thank you so very much for your help in advance.
All the best,
Marianne
You can use COUNT(DISTINCT unique_id) to find the values of consecutive_id that have different unique_id.
SELECT consecutive_id
FROM test
GROUP BY consecutive_id
HAVING COUNT(DISTINCT unique_id) > 1
You can then join this with your original table, group by both unique_id and consecutive_id, and get the rows that just have a count of 1, which means they're not equal to the rest of the group.
Since there can be multiple outliers, you need another level of subquery that just gets the minimum outlier for each consecutive ID.
SELECT consecutive_id, MIN(id) as id
FROM (
SELECT a.consecutive_id, MIN(id) AS id
FROM test AS a
JOIN (
SELECT consecutive_id
FROM test
GROUP BY consecutive_id
HAVING COUNT(DISTINCT unique_id) > 1) AS b
ON a.consecutive_id = b.consecutive_id
GROUP BY a.consecutive_id, a.unique_id
HAVING COUNT(*) = 1) AS x
GROUP BY consecutive_id