PHP MySQL selects all results with ASC references

advertisements

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.

Good luck.