Help: Select row with largest column x value

jasonla

I'm trying to select a row that contains the largest value of column x.

Column x is an int(11) time timestamp generated by PHP's time() function.

Column x is indexed. But when I run the query (see below), MySQL doesn't use the index because I have no WHERE clause. Is there a better way of doing this?

SELECT i.*, MAX(i.issue_pubdate) 
FROM issue i
GROUP BY i.issue_id ORDER BY i.issue_pubdate
DESC LIMIT 1

bigswifty

nevermind..see my next post

jasonla

You can't use the MAX() function in the WHERE clause.

bigswifty

doh! how true that is 😛

no you can't use max in the where clause... not enough coffee 🙂

I also blew it on the group by clause which is needed to mix collums with max.

I guess I don't understand the question, as it is the query works just fine for me

SELECT *, MAX(price) FROM tradebooks GROUP BY title ORDER BY price DESC LIMIT 1

gives a fairly fast and 100% accurate result, why do you need indexing? are you hoping to speed it up a bit?
can you even index something like a time stamp or int?
"show me the biggest number" does not seem like a query that would benefit from indexing, tho I may well be wrong

ok back to bed for me 😛

Dr_John

Select * from issue
where issue_pubdate = (Select max(issue_pubdate ) from issue) ;

jasonla

Is there a way to do it without using a sub-select?

Dr_John

You can't do this by looking at a record and just saying "is this equal to the max?" without first knowing the max. Same for average and minimum.

To get the max you must first go through every record and calculate its value. Then the value for every record is compared to the max and the where clause assessed. This doesn't mean that every row will result in the subquery being run again, and again, and again. The query optimiser will spot this, decide to find the max, store it in memory, then go through and do the comparison.