I beleive the below solution will not detect rows missing from the very
top of the table,
try this...
select ifnull((select max(a.test_id) +1
from tests a
where a.test_id<b.test_id),1) as 'from'
,b.test_id -1 as 'to'
from tests b
left outer join tests x
on x.test_id=b.test_id -1
where x.test_id is NULL and b.test_id>1
order by 1
Ed
________________________________
From: Peter Brawley [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 18, 2005 10:18 AM
To: [EMAIL PROTECTED]
Cc: Scott Hamm; 'Mysql '
Subject: Re: Deleted rows
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
-----
[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]>
<mailto:[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
________________________________
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
