High so High

[Anon]

my mysql skillz are really rusty, and now i need them for a project so i have to re-familiarize myself with it again, and i need some help.

i have a table, and in one set i have an auto incrementing number. what i want to do, is query for a record whose (auto incremented) number is the highest or has the largest value without having any idea what that number is, and i only want to return that one record.

so basically i want a:

select * from table where (field ??'s number is the highest) limit 1,1;

please help.

[Anon]

select * from table order by (auto_field_name) desc limit 1,1;

• james

[Anon]

thanks james

[tasistro]

I'd like to know how this would differ in speed as compared to ...
select @max_ID:=max(%IDfield%) from %table%;
select * from %table% where ID=@max_ID;

They are two sqls, but you don't need to order by anything. I think it would be faster. You don't fetch data after the first sql, since @max_ID should stay in the server. The result is retrieved only after the second sql.

Saludos
Gerardo

[Anon]

that is two queries
try this for faster results
SELECT * from %table% where ID=(SELECT max(%IDfield%) from %table%);
As this is only one query and will attempt to pull data only once. The way you have it it will search the max id field only first, then get all fields on the second.

[tasistro]

Mysql 3.whatever doesn't handle nested sql.

Saludos
Gerardo

[[deleted]]

Clever, very clever! 🙂

[[deleted]]

cough and that's one query? :-)
Remember, both queries still do the same.

oh.. and the fact that MySQL doesn't do subqueries is a bit of a bummer too.

[tasistro]

grin!

Thanks, vincent.

😉

Saludos
Gerardo

[Anon]

man, i just know this kid is doing an insert and trying to get the id for that record...

jeremy, if that is the case and you are using mysql, just use mysql_insert_id()