From: Peter Brawley Date: August 18 2005 4:17pm Subject: Re: Deleted rows List-Archive: http://lists.mysql.com/mysql/188011 Message-Id: <4304B4B3.7080606@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-4304B4B44564=======" --=======AVGMAIL-4304B4B44564======= Content-Type: multipart/alternative; boundary=------------040104050302060509020302 --------------040104050302060509020302 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 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=sgreen@stripped > >------------------------------------------------------------------------ > >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 > > --------------040104050302060509020302 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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=sgreen@stripped 

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
--------------040104050302060509020302-- --=======AVGMAIL-4304B4B44564======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-4304B4B44564=======--