List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:October 8 2008 9:24pm
Subject:Re: Finding gaps
View as plain text  
>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
>
>   

Thread
Finding gapsJerry Schwartz17 Sep
  • Re: Finding gapsStut17 Sep
    • Re: Finding gapsPeter Brawley17 Sep
      • RE: Finding gapsJerry Schwartz8 Oct
        • Re: Finding gapsGerald L. Clark8 Oct
          • RE: Finding gapsUS Data Export8 Oct
        • Re: Finding gapsPeter Brawley8 Oct
          • RE: Finding gapsUS Data Export9 Oct
            • Re: Finding gapsPeter Brawley9 Oct
              • RE: Finding gapsJerry Schwartz10 Oct
                • Re: Finding gapsMoon's Father25 Oct
                  • Re: Finding gapsMoon's Father25 Oct
                  • RE: Finding gapsUS Data Export27 Oct
    • RE: Finding gapsJerry Schwartz17 Sep
      • Re: Finding gapsStut18 Sep
        • Re: Finding gapsMr. Shawn H. Corey18 Sep
        • RE: Finding gapsJerry Schwartz18 Sep
          • Re: Finding gapsMike Diehl18 Sep
            • RE: Finding gapsJerry Schwartz18 Sep
    • RE: Finding gapsMartin Gainty18 Sep
      • RE: Finding gapsJerry Schwartz18 Sep
    • Re: Finding gapsJoerg Bruehe18 Sep
    • Re: Finding gapsJoerg Bruehe18 Sep
      • Re: Finding gapsStut18 Sep
        • Re: Finding gapsMr. Shawn H. Corey18 Sep