List:General Discussion« Previous MessageNext Message »
From:Scott Hamm Date:August 18 2005 4:06pm
Subject:Re: Deleted rows
View as plain text  
On 8/18/05, SGreen@stripped <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 


Something similiar to this query for MS SQL (very time consuming)

SET NOCOUNT ON
DECLARE 
@mindate smalldatetime,
@maxdate smalldatetime
SET @mindate='2005-08-01'
SET @maxdate='2005-08-31'
CREATE TABLE 
#count (ID int);
DECLARE 
@low int,
@high int
SET 
@low=(
SELECT 
MIN(QAErrors.ID)-1 
FROM 
QAErrors 
LEFT JOIN 
QA Q 
ON Q.ID=QAErrors.QAID 
WHERE 
KeyDate 
BETWEEN 
@mindate 
AND 
@maxdate
)
SET 
@high=(
SELECT 
MAX(QAErrors.ID) 
FROM 
QAErrors 
LEFT JOIN 
QA Q 
ON 
Q.ID=QAErrors.QAID 
WHERE 
KeyDate 
BETWEEN 
@mindate 
AND @maxdate
)
DECLARE 
@counter INT
SET 
@counter = @low
WHILE 
@counter < @high
BEGIN
SET 
@counter = @counter + 1
INSERT INTO 
#count 
VALUES 
(@counter)
END
SET NOCOUNT OFF
SELECT 
@mindate AS 'From',
@maxdate AS 'To',
count(*) AS 'Total Deleted'
FROM 
#count C
LEFT JOIN 
QAErrors QE 
ON 
QE.ID=C.ID
LEFT JOIN
QA Q
ON
Q.ID=QE.QAID
WHERE
Q.ID <http://Q.ID> is null;
DROP TABLE 
#count;

-- 
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

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