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 <[EMAIL PROTECTED]>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 < > [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 > -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
