List:General Discussion« Previous MessageNext Message »
From:US Data Export Date:October 8 2008 8:54pm
Subject:RE: Finding gaps
View as plain text  

>-----Original Message-----
>From: Gerald L. Clark [mailto:gerald_clark@stripped]
>Sent: Wednesday, October 08, 2008 4:44 PM
>To: Jerry Schwartz
>Cc: mysql@stripped
>Subject: Re: Finding gaps
>
>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;
>>
>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 < `To`
[JS] That didn't seem to work:

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 < `To`;
ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause'



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