List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 18 2005 4:17pm
Subject:Re: Deleted rows
View as plain text  
Scott, Shawn,

 >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.

Right! For sequences longer than 1 you need something like...

SELECT
  a.id+1 AS 'Missing From',
  MIN(b.id)-1 AS 'To'
FROM test AS a, test AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING a.id + 1 < MIN(b.id)
ORDER BY 1;

PB

-----

SGreen@stripped wrote:

>
> 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
>
>------------------------------------------------------------------------
>
>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
>  
>

Attachment: [text/html]
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
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