Link to home
Start Free TrialLog in
Avatar of emann17

asked on

Mysql Sequential Numbers missing

Hello, I have a simple query question I'm sure for most but I'm stumped.

I have a very simple table with two columns.


In some cases Priority may not be sequential I need to get the gaps so I can post them as available in a PHP page. But I need to group by Account so gaps of other accounts don't show as available.

Tried this but I'm not getting the desired result:

SELECT a.Priority + 1 FROM Peers AS a WHERE  NOT EXISTS (SELECT b.Priority FROM Peers AS b WHERE a.Priority + 1 = b.Priority and Account=1) and Account=1 GROUP BY a.Priority;

I'm sure its something simple. Any help would be great, thank you!
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

What kind of column is Priority and is it an autoincrement column?
Avatar of emann17


No auto increment. Values can be 1,2,3,4,5,6,7 or 1,5,6,9,15,30.
The following old-school approach works in SQL Server:

SELECT p2.Account, MAX(COALESCE(p1.Priority, 0) + 1) AS StartGap, 
    p2.Priority - 1 AS EndGap
    Peers p2 ON p1.Account = p2.Account AND p1.Priority < p2.Priority
WHERE p2.Priority > 1
GROUP BY p2.Account, p2.Priority - 1
HAVING MAX(COALESCE(p1.Priority, 0)) < (p2.Priority - 1)
ORDER BY p2.Account, MAX(COALESCE(p1.Priority, 0) + 1), p2.Priority - 1

Open in new window

I do not have access to a MySQL server right now to test it there.
Avatar of emann17


mysql throws a:

ERROR 1054 (42S22): Unknown column 'p2.Priority' in 'having clause' Also I don't see where to identify the Account value.
Avatar of Ovid Burke
Ovid Burke
Flag of Saint Vincent and the Grenadines image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What is it you're trying to do with the gaps? Fill them in on inserts, update them to eliminate the gaps or what?
On second look my solution gets gaps alright, except if the gap begins at 1. matthewspatrick's solution is better.

To fix the error in the HAVING clause, change (p2.Priority - 1) to EndGap
So are you just trying to find the gaps in the priority values for each account or a specific account?