Do you mean you want to be able to display the record number as sorted by the auto-increment field, rather than the auto-increment field itself? Or do you just want the total number of records? Or do you just want the highest current value of the auto-increment field?
The latter two are easy: SELECT COUNT(*) FROM table; SELECT MAX(auto_inc) FROM table; Offhand, I do not know how to do the first. 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: Jason Pruim [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 29, 2007 3:16 PM > To: Shawn Green > Cc: MySQL List > Subject: Re: Reset a auto increment field? > > > On Aug 29, 2007, at 2:30 PM, Shawn Green wrote: > > > Hi Jason, > > > > Jason Pruim wrote: > >> Is there away to reset an auto incrementing field count? I have a > >> database that currently has 935 records in it but because I have > >> deleted a few the current number used for NEW records is 938 :) > >> How can I get it to count the records and assign a record number > >> based on the total count? > >> Hope that makes sense! Thanks for looking! :) > > > > Actually, it doesn't make sense and for the very reason you are > > trying to use it. At some point in history you had a record # 936. > > Because that record once existed, there may have been one or > > several things associated with it. Imagine the confusion > that would > > ensue if the Social Security administration recycled an already > > issued number just as soon as the person using it died. > > > > The safest thing to do is to pretend that the auto-incrementing > > field is an internal, non-editable field. Should you have gaps in > > your auto-inc values treat them as normal conditions of having an > > active database. > > > > For another instance, assume that you are auto-incrementing the > > serial numbers to various items in an inventory control system. If > > an item is destroyed or taken out of use, you probably want > to move > > that record from an "activeitems" table to some other location. > > Would you want to re-issue those numbers to newly purchased items > > just to fill in the gaps in the "activeitems" table? Of course not. > > > > Now, with the understanding that doing this on a regular basis > > would be wrong, here is how to do it anyway: Use the > > "auto_increment=" option to an ALTER TABLE statement like this > > > > ALTER TABLE mydata AUTO_INCREMENT=936; > > > > (alter table) > > http://dev.mysql.com/doc/refman/5.0/en/alter-table.html > > (for the definition of "table option") > > http://dev.mysql.com/doc/refman/5.0/en/create-table.html > > I see what you are getting at with this, and have decided that > mucking around with auto incrementing values doesn't exactly fit in > with the way databases were designed to work. > > Somehow though, I still need to supply this whether I end up > adding a > "Record" number field in the database, and then through php (The way > the database is going to be accessed) assigned a record number to > that field based on the total rows, and display that number rather > then the internal record number. > > This is getting complicated :) > > > > > > > -- > > Shawn Green, Support Engineer > > MySQL Inc., USA, www.mysql.com > > Office: Blountville, TN > > __ ___ ___ ____ __ > > / |/ /_ __/ __/ __ \/ / > > / /|_/ / // /\ \/ /_/ / /__ > > /_/ /_/\_, /___/\___\_\___/ > > <___/ > > Join the Quality Contribution Program Today! > > http://dev.mysql.com/qualitycontribution.html > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql? > > [EMAIL PROTECTED] > > > > > > -- > > Jason Pruim > Raoset Inc. > Technology Manager > MQC Specialist > 3251 132nd ave > Holland, MI, 49424 > www.raoset.com > [EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
