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:[EMAIL PROTECTED]
Sent: Wednesday, October 08, 2008 5:25 PM
To: Jerry Schwartz; mysql@lists.mysql.com
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:[EMAIL PROTECTED]
Sent: Wednesday, September 17, 2008 5:26 PM
To: Stut; mysql@lists.mysql.com
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
