Suppose I have the following table:
ID|Reference 1 |23 1 |27 1 |47 1 |48 2 |16 2 |17 2 |18 2 |33 3 |16 3 |19 3 |22 4 |2 5 |6 5 |8 5 |7 5 |12
I want to select all the ID's that have at least 2 ASC references with a difference of 1. So for instance when I execute the query, I want a return of:
ID|Reference 1 |47 1 |48 2 |16 2 |17 2 |18 5 |6 5 |8 5 |7
Ofcourse, I could use a query like:
SELECT ID, Reference FROM Table ORDER BY Reference ASC
and loop through the results and check it manually. However, I am wondering whether MySQL could achieve this in a single query and pop back the right results, since this will optimize performance (I guess)
It is strange a field called ID that is not unique, you probably have a good reason.
You can try with a query like
SELECT ID, Reference FROM Table WHERE EXISTS (SELECT * FROM Table T WHERE T.ID = Table.ID AND T.Reference = Table.Reference - 1) OR EXISTS (SELECT * FROM Table T WHERE T.ID = Table.ID AND T.Reference = Table.Reference + 1)
That means "get elements that have another element with same ID and a Reference with distance 1 (lower or higer)". It will not return exactly the ID's (you can try with SELECT DISTINTC(ID)...) it will return a result like your example with ID, Reference pairs where IDs will be not unique.
I am not sure if this will be more efficient, it probably depends on how many records you have in your table and how many records will match your condition.