Link to home
Start Free TrialLog in
Avatar of emann17
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.

Peers(Account,Priority)

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
emann17

ASKER

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
FROM Peers p1 RIGHT JOIN
    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

ASKER

mysql throws a:

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

Link to home
membership
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?