get row with higest value.

[seany123]

this is a php/mysql question.

 

basically i want to get the row with the highest value

 

value being episode_id

 

so something like

 

$query = mysql_query("SELECT * FROM table WHERE episode_id=highestvalue");

 

or i dont know something like that.

 

any help would be great!

 

thanks

 

 

[Maq]

Why don't you use the mysql MAX() function?

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_max

 

And how does this relate to PHP exactly?

[seany123]

i havnt looked into the MAX funtion.. thanks ill look.

 

really this doesnt relate to php other than i would want to be using php with the row returned... (sorry i always get confused as to where mysql ends and php begins).

 

 

currently im thinking of something like this as a temporary fix.

 

    $query = mysql_query("SELECT * FROM table ORDER BY episode_id DESC LIMIT=1");

[Maq]

Yes that would work too but I'm not sure which one would be faster, if I had to guess, the MAX() function.

[seany123]

how could i include the max() function in with the current query im using?

 

$query = mysql_query("SELECT * FROM table max(episode_id)");

 

anything like that?

[fenway]

Maybe *in* the column list.

[sptrsn]

ok. I'm a total noob, but this is actually one that I can answer because I constantly fought a similar problem, so I'm kind of excited to finally give back.

 

The max() function will only return a value, the highest value, but if you're like me, you assume it will bring you the entire row with the highest value. It doesn't. I gives you a simple value. At that point, you have to select the row that matches that value. Only after you've selected the row can you join it with other tables. 

select * from t2 as t2 join 

(select col2, max(id) as id from t2 group by col2)as mx

on t2.id=mx.id

 

 

 

Hope this helps.

[The Little Guy]

I found this:

 

I have a table with about 300M rows.

 

Select max(foo) from bar; -- takes about 15 sec. to run

 

Select foo from bar order by foo desc limit 1; -- takes 3 sec. to run

 

So in conclusion, I would say "order by" is faster than max

[The Little Guy]

hmm, it looks that that data above is wrong, I just did that on a table with 123,190,064 rows, and they both took 0 seconds. obviously the person didn't have his/her tables indexed well.

[Maq]

From an answer on stackoverflow:

With an index on order_date, they are of same performance.

 

Without an index, MAX is a little bit faster, since it will use Stream Aggregation rather than Top N Sort.

[fenway]

Quassnoi's almost always right -- but without an index, mysql would have to build one first, and then it's hard to believe there would be a different -- but hey.