List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:October 10 2008 3:34pm
Subject:RE: Finding gaps
View as plain text  
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
 
  


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