Sequential

Last Post 20 Apr 2005 09:04 AM by nosepicker. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Krypto
New Member
New Member

--
15 Apr 2005 10: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 09: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.
You are not authorized to post a reply.

Acceptable Use Policy