List:General Discussion« Previous MessageNext Message »
From:emierzwa Date:August 18 2005 6:35pm
Subject:RE: Deleted rows
View as plain text  
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:peter.brawley@stripped] 
Sent: Thursday, August 18, 2005 10:18 AM
To: SGreen@stripped
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

-----

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


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