On 8/18/05, [EMAIL PROTECTED] <[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]> 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]
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
