How to find the highest number in a mysql table

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: How to find the highest number in a mysql table by igelkott (Priest) on May 10, 2008 at 01:40 UTC
Might not understand the problem but an `auto_increment` typically means that you don't need to know the ID for the next insert. You can skip that column (if you have a list of the others) or enter `null` or even 0. If you need to know the ID after an insert, use `my $last_id = $dbh->{'mysql_insertid'};`. This can useful to insert associated data into another table.	[reply] [d/l] [select]
Re^2: How to find the highest number in a mysql table by Anonymous Monk on May 10, 2008 at 01:43 UTC
I can think of many reasons why knowing the ID in a case like this would be required. Let's say your images for this gallery are numerically numbered, 00001, 00002, 00003... And they're numbered after the id column. You have to read from the database BEFORE inserting another row to see what the next image will be labled.	[reply]
Re^3: How to find the highest number in a mysql table by ysth (Canon) on May 10, 2008 at 02:03 UTC
That's typically avoided because of the race condition. You could insert the row with a "not yet published" flag, and only create the file after the insert. Or have a separate table with just the auto-increment field and allocate ids whenever you want to use for the filenames and insertion into the main table. -- Online Fortune Cookie Search	[reply]
Re^4: How to find the highest number in a mysql table by sulfericacid (Deacon) on May 10, 2008 at 02:42 UTC
Re^4: How to find the highest number in a mysql table by pc88mxer (Vicar) on May 10, 2008 at 06:24 UTC
Re^3: How to find the highest number in a mysql table by CountZero (Bishop) on May 10, 2008 at 15:55 UTC
It is generally a bad idea to have your key field(s) being anything else than an abstract number or code. All it has to be is to be unique. If it refers (even indirectly) to some real world properties, sooner or later you are going to run into trouble. Believe me, I have been bitten by it several times. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply]
Re: How to find the highest number in a mysql table by sulfericacid (Deacon) on May 10, 2008 at 00:53 UTC
`my $data = qq(SELECT MAX(id) FROM table); # +1` [download] or `my $data = qq(SELECT id FROM table ORDER BY id DESC); # +1` [download] "Age is nothing more than an inaccurate number bestowed upon us at birth as just another means for others to judge and classify us" sulfericacid	[reply] [d/l] [select]
Re^2: How to find the highest number in a mysql table by kyle (Abbot) on May 10, 2008 at 02:03 UTC
There are a couple of problems here. The basic problem is that this only tells you what the last ID in the table was (+1), not what the next ID will be. If I insert a record and then delete it, that ID will not be reused, but your solution assumes it will. The next ID will actually be the highest +2. I could do this with any number of records. The other problem is in your second solution: `my $data = qq(SELECT id FROM table ORDER BY id DESC); # +1` [download] This selects every row in the table. You really want to select only one: `my $data = qq(SELECT id FROM table ORDER BY id DESC LIMIT 1); # +1` [download] ...but this still suffers from the first problem.	[reply] [d/l] [select]
Re: How to find the highest number in a mysql table by Anonymous Monk on May 10, 2008 at 15:01 UTC
my $dbh = DBI->connect(...); my $next_pic = $dbh->selectrow_hashref("SELECT MAX(image_id)+1 AS nextid from images")->{'nextid'); Supposing that your table is called 'images' and the autoincrement column name is 'image_id'.	[reply]