Sequential

Last Post 20 Apr 2005 08:04 AM by nosepicker. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Krypto
New Member
New Member

--
15 Apr 2005 09:45 AM
We have a numeric field that we want to return the any non-sequential numbers. The numbers are usually sequential but sometimes some are missing. We want only the ones that are missing. What's the easy way to do this?

Thanks!
nosepicker
New Member
New Member

--
20 Apr 2005 08:04 AM
See if this works for you:

SELECT YourCol +1 FROM YourTable A
WHERE NOT EXISTS (SELECT 1 FROM YourTable B WHERE A.YourCol +1 = B.YourCol)
AND YourCol <> (SELECT MAX(YourCol) FROM YourTable)

NOTE: If you have a gap of 2 or more between numbers, you will have to run this query again, after inserting the initial set of missing numbers, and keep running the query until it returns no records.


Acceptable Use Policy
---