Getting the highest value

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Getting the highest value

Post by MarK (CZ) »

Hi all,

here's my problem: I need to insert a record to the database. There is an `id` with auto_increment but there's also a `key` column. That can be filled by user but what i need is - if user doesn't entry anything, then I want `key` to be {highest value of `key` in whole table + 1}. I could do this via PHP query (getting the highest value) but is there a way how could I get the highest value directly in the MySQL query? Like this:

Code: Select all

INSERT INTO table (key, text) VALUES (highest_number() + 1, 'Some text')
And if I'd have to do it through php: Isn't there a risk that someone will use this highest key before me? Example:

Code: Select all

<?php
$sql = "SELECT key FROM table ORDER BY key DESC LIMIT 1"; // get the highest `key` in the table
$result = mysql_query($sql) or Die("Error<br>\r\n".mysql_error()."<br>\r\n$sql");
$row = MySQL_Fetch_Row($result);
$key = $row[0];

//
// Someone else stores something in the db with the same `key`
//

$sql = "INSERT INTO table (key, text) VALUES ($key, 'Some text');"; // key will NOT be unique now
$result = mysql_query($sql) or Die("Error<br>\r\n".mysql_error()."<br>\r\n$sql");

?>
Thx
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

the easiest is to use the AUTOINCREMENT option.

to get the maximum of something

Code: Select all

SELECT MAX(column)
FROM foo
If you use transactions (or something to fake it like row locking) a set of queries will be handled as if were 1 query. (and thus help you get round the concurrency problems)
Last edited by timvw on Mon Sep 27, 2004 3:10 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

unless your version of mysql supports sub-queries, I don't remember a way to do it without 2 queries. Although I may be in a mind-fog :P
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Post by MarK (CZ) »

timvw wrote:the easiest is to use the AUTOINCREMENT option.
I cannot use that as I already use auto_increment on `id`. And I need `key` to be set by user in some cases.
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post by hedge »

Code: Select all

insert into table (key, text)
select max(key)+1, 'Some Text' from dual;
That's Oracle syntax, dual is just a pseudo-table with a single row, you could create a dummy table to accomplish the same thing.
Last edited by hedge on Mon Sep 27, 2004 6:01 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

a dummy table for 1 value is kinda a waste to me.. he's using mysql btw. :)
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Post by MarK (CZ) »

hedge wrote:

Code: Select all

insert into table (key, text)
select max(key)+1, 'Some Text' from dual;
That's Oracle syntax, dual is just a pseudo-table with a single row, you could create a dummy table to accomplish the same thing.
Well, I'm running it without another table and I guess it works as it should.

Code: Select all

$sql = "INSERT INTO news (`key`) ".
       "SELECT MAX(`key`)+1 FROM news";
Post Reply