List:General Discussion« Previous MessageNext Message »
From:Moon's Father Date:October 25 2008 7:37am
Subject:Re: Finding gaps
View as plain text  
Or you can create a temporary table including a auto_increment attribute and
fill it with continuous numbers.
Then simply use it to left join the original table.
On Sat, Oct 25, 2008 at 2:57 PM, Moon's Father <yueliangdao0608@stripped>wrote:

> Create an extra trigger on that table with delete event.Then the deleted
> item will be recorded in the database.
>
>
> On Fri, Oct 10, 2008 at 11:34 PM, Jerry Schwartz <
> jschwartz@stripped> wrote:
>
>> Thanks.
>>
>>
>>
>> Although I've been around SQL for quite a while, I've never really gotten
>> the hang of self-joins.
>>
>>
>>
>> From: Peter Brawley [mailto:peter.brawley@stripped]
>> Sent: Wednesday, October 08, 2008 8:22 PM
>> To: US Data Export; mysql@stripped
>> Subject: Re: Finding gaps
>>
>>
>>
>> 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
>>
>>
>>
>
>
> --
> I'm a MySQL DBA in china.
> More about me just visit here:
> http://yueliangdao0608.cublog.cn
>



-- 
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

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