From: Peter Brawley
Date: October 9 2008 12:22am
Subject: Re: Finding gaps
List-Archive: http://lists.mysql.com/mysql/214821
Message-Id: <48ED4EAA.1070008@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------030309070405070607060607"

--------------030309070405070607060607
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jerry,

Here is a workaround for 4.1.22:

SELECT
  a.id+1 AS 'Missing From',
  MIN(b.id) - 1 AS 'To'
FROM tbl AS a, tbl AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING `Missing From` < MIN(b.id);
+--------------+------+
| Missing From | To   |
+--------------+------+
|            3 |    3 |
|            5 |   17 |
+--------------+------+

PB

US Data Export wrote:
> Well, 5.x accepted the query. It's been running for awhile, now, so I'll
> find out later if it did what I need.
>
>   
>> -----Original Message-----
>> From: Peter Brawley [mailto:peter.brawley@stripped]
>> Sent: Wednesday, October 08, 2008 5:25 PM
>> To: Jerry Schwartz; mysql@stripped
>> Subject: Re: Finding gaps
>>
>>     
>>> I must be missing something obvious; or does this not work in 4.1.22?
>>>       
>> Looks like a 4.1.22 bug.
>>
>> PB
>>
>> Jerry Schwartz wrote:
>>     
>>> I'm finally getting back to this issue, and I've read the bits on
>>> artfulsoftware. The example
>>>
>>> SELECT
>>>   a.id+1 AS 'Missing From',
>>>   MIN(b.id) - 1 AS 'To'
>>> FROM tbl AS a, tbl AS b
>>> WHERE a.id < b.id
>>> GROUP BY a.id
>>> HAVING a.id < MIN(b.id) - 1;
>>>
>>> Looks like exactly what I want. However, when I try it (prod is my
>>>       
>> tbl,
>>     
>>> prod_num is my id) I get
>>>
>>> mysql> select a.prod_num + 1 AS `Missing From`,
>>>     ->   MIN(b.prod_num - 1) AS `To`
>>>     -> from prod as a, prod as b
>>>     -> where a.prod_num < b.prod_num
>>>     -> group by a.prod_num
>>>     -> having a.prod_num < min(b.prod_num) -1 ;
>>> ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause'
>>>
>>> I must be missing something obvious; or does this not work in 4.1.22?
>>>
>>> Regards,
>>>
>>> Jerry Schwartz
>>> The Infoshop by Global Information Incorporated
>>> 195 Farmington Ave.
>>> Farmington, CT 06032
>>>
>>> 860.674.8796 / FAX: 860.674.8341
>>>
>>> www.the-infoshop.com
>>> www.giiexpress.com
>>> www.etudes-marche.com
>>>
>>>
>>>       
>>>> -----Original Message-----
>>>> From: Peter Brawley [mailto:peter.brawley@stripped]
>>>> Sent: Wednesday, September 17, 2008 5:26 PM
>>>> To: Stut; mysql@stripped
>>>> Subject: Re: Finding gaps
>>>>
>>>>
>>>>         
>>>>> Is there any elegant way of finding the gaps?
>>>>>
>>>>>           
>>>> You'll find some ideas under (and near) "Find missing numbers in a
>>>> sequence" at http://www.artfulsoftware.com/infotree/queries.php.
>>>>
>>>> PB
>>>>
>>>> -----
>>>>
>>>> Stut wrote:
>>>>
>>>>         
>>>>> On 17 Sep 2008, at 22:12, Jerry Schwartz wrote:
>>>>>
>>>>>           
>>>>>> I have records that should be sequentially (not auto-increment)
>>>>>> numbered,
>>>>>> but there are gaps. Is there any elegant way of finding the gaps?
>>>>>>
>>>>>>             
>>>>> Why do they need to be sequential? When this requirement comes up
>>>>>           
>> it's
>>     
>>>>> usually for illogical reasons.
>>>>>
>>>>> -Stut
>>>>>
>>>>> --------------------------------------------------------------------
>>>>>           
>> --
>>     
>>>> --
>>>>
>>>>         
>>>>> No virus found in this incoming message.
>>>>> Checked by AVG - http://www.avg.com
>>>>> Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date:
>>>>>
>>>>>           
>>>> 9/17/2008 5:07 PM
>>>>
>>>>         
>>>
>>> ----------------------------------------------------------------------
>>>       
>> --
>>     
>>> No virus found in this incoming message.
>>> Checked by AVG - http://www.avg.com
>>> Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date:
>>>       
>> 10/7/2008 6:40 PM
>>     
>>>       
>
>
>
>
>   
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM
>
>   

--------------030309070405070607060607--