Thanks for the suggestion. Unfortunately that doesn't fit my need, because I need to go back in time.
From: Moon's Father [mailto:[EMAIL PROTECTED] Sent: Saturday, October 25, 2008 2:57 AM To: Jerry Schwartz Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Finding gaps 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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 8:22 PM To: US Data Export; mysql@lists.mysql.com 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:[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 -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
