List:General Discussion« Previous MessageNext Message »
From:Moon's Father Date:October 25 2008 6:57am
Subject:Re: Finding gaps
View as plain text  
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

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