How do I find the maximum number in a column

same as finding the entry I just added on an auto increment

[kuper20]

Hi,

I want to do something pretty simply but I can't seem to figure out how to do it, I'm assuming there may be just some max() function.

I'm adding to a table that has an id number that is automatically incremented, so when I add a row I don't give it the number or know it because it does it automatically. How do I get that number right away?

Thanks for the help

[rocknbil]

There is a last_insert function I believe, you could look into it. I'm lazy and just do another query after the insert. :-)

select rec_id from table order by rec_id desc limit 1;

[LifeinAsia]

I'm lazy and just do another query after the insert.

Inefficient, but works if you are the only one entering new data. Otherwise, you're asking for trouble.

It depends on the DB you're using. Many (most?) have some sort of function to return the identity value of the row just entered.

[rocknbil]

works if you are the only one entering new data.

I was speaking in terms of a scripted solution, what have you got in your tool box? In a high access situation where a second user might add a record in the nano second before the script can get the last record, you might do this:

- create a unique id. In reality, it's ALWAYS better to use a unique id for a handle on a database record anyway instead of the auto increment numbers. If you ever need to move the DB, this would avert joined tables hell.

Part of creating this unique id would entail making sure it doesn't already exist in the DB, and keep getting a unique number until it does.

- query the table as above, but add a where to match on this ID.

$unique = &some_function_for_unique_id;

insert into table (unique_id,fname,lname. . . .

Then get the last inserted record by

select id from table where unique_id='$unique' order by id desc limit 1;

Perhaps I misunderstood the question. If the O.P. is writing a script that inserts a record and wants to return to edit mode of that very same record after insert, it's extremely unlikely another process will sneak in a new record before it can perform the two queries. But the possibility does indeed exist, and the above solution is probably a better choice.

[LifeinAsia]

That works too. We have some really big tables with lots of visitors, so I hate to use extraneous DB calls if I don't have to.

If using MS SQL, you can use the following:
INSERT INTO Whatever(Fieldname)
VALUES(value);
SELECT ThisID = @@identity;

I'm sure MySQL, et al have similar functions.

And the inserts don't necessarily have to be nano seconds apart to cause a problem with the first method suggested. If you have a lot of transactions queued up, it could easily be several seconds before the write and subsequent read to find the ID inserted. We've seen it happen several times. Then again, we do run a very busy site- the main DB grows about 300-400 MB/day.

[noyearzero]

for php mysql the function is mysql_insert_id(). if you don't set any parameters it will grab the auto increment value of your last INSERT.

[rocknbil]

Yes, but that's a predefined function in PHP that does the same thing as discussed above. It's a function of the scripting, not a database command.

Got off my laziness and looked it up for you. The mysql function to get the last autoincrement id is last_insert_id() [dev.mysql.com] (link to documentation.)

select last_insert_id();

If you insert multiple rows, it's only going to get the first one you insert. See the docs for examples, they also explain why this is.