Re: Find Missing Sequence Numbers

[T Cunningham]

I don't think you can get a query which will return a row for each 
missing result, because a query can only return data that's there, not 
data that's missing. (although if you generated another table with all 
the numbers 1 through 1,000,000 that would work).

Anyway, to get one result row per *gap* use:

SELECT * FROM table LEFT JOIN table2 ON table2.id=(table.id+1) WHERE 
table2.id IS NULL;

Tom.

Mark Riehl wrote:

All - I've got a table that has an unsigned int that stores increasing
sequence numbers that are stored in UDP payloads.  Occasionally, messages
get lost and we'll have missing numbers in the sequence.  These are not
auto_incrementing columns, the sequence numbers are assigned by the
application that generates the network traffic.
What's the easiest way to identify gaps in the sequence numbers?  For
example, sequence numbers might look like the following: 100, 101, 102, 112,
...
How can I easily detect the gaps?

Thanks,
Mark
Mark Riehl 
Agile Communications, Inc. 
Email: [EMAIL PROTECTED] 
Office: (732) 528-9305 
Fax: (732) 528-9308 
Cell: (732) 996-7630 



 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]