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:japruim@raoset.com]
> 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/...ate-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?
> > unsub=japruim@raoset.com
> >
> >
>
> --
>
> Jason Pruim
> Raoset Inc.
> Technology Manager
> MQC Specialist
> 3251 132nd ave
> Holland, MI, 49424
> www.raoset.com
> japruim@raoset.com
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
>
>