List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 18 2005 3:46pm
Subject:Re: Deleted rows
View as plain text  
Peter, 

Your query may work for data with single-row gaps (like his example data) 
but it will not work if the sequence skips more than one number.

Look at this sequence: 1,2,3,8,9,10

The OP would like to detect that 4,5,6, and 7 are missing from the 
sequence. Your query would have only found that 7 was missing.

Nice try, but sorry. It just won't meet the need.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Peter Brawley <peter.brawley@stripped> wrote on 08/18/2005 10:56:34 
AM:

> Scott,
> 
> >How do I execute a query that shows missing ID's like so:
> 
> SELECT id AS i 
> FROM tbl
> WHERE i <> 1 AND NOT EXISTS( 
>   SELECT id FROM tbl WHERE id = i - 1
> );
> 
> PB
> 
> -----
> 
> Scott Hamm wrote: 
> If I got a table as follows:
> 
> 
> ID foo
> 1 12345
> 2 12346
> 4 12348
> 6 12349
> 7 12388
> 9 12390
> How do I execute a query that shows missing ID's like so:
> 
> 3
> 5
> 8
> 
> I wouldn't expect for it to show deleted data that was deleted, just 
show 
> the "skipped" ID's.
> 
> That way I determine if operator deleted too much (cheating at QC)
> 
> Is it possible?
> 
> 
> 
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 
8/17/2005
>   No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 
8/17/2005
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
Thread
Deleted rowsScott Hamm18 Aug
  • Re: Deleted rowsSGreen18 Aug
  • Re: Deleted rowsPeter Brawley18 Aug
    • Re: Deleted rowsSGreen18 Aug
      • Re: Deleted rowsScott Hamm18 Aug
      • Re: Deleted rowsPeter Brawley18 Aug
RE: Deleted rowsemierzwa18 Aug