How to let mysql return the row with highest bullishness value?

Question

Consider the following table:

stock    _date       return2  bullishness
-----------------------------------------
1        2011-03-14  0.10     0.75
2        2011-03-14  0.05     1.75
3        2011-03-14  0.11     1.00
1        2011-03-15  0.03     1.99
2        2011-03-15  0.30     4.00
3        2011-03-15  0.00     0.60

I'm looking for a query which will:

• look at the bullishness for all stocks for a specific _date
• and return the row with the highest bullishness

I would have expected, for 2011-03-14 to accomplish this with

SELECT MAX(bullishness) as bullishness, stock, return2 
FROM stocks 
WHERE _date = '2011-03-14'

But this just always returns the first row, in this case:

1   2011-03-14  0.10  0.75

While I would have expected the query to return the second row (since it has a bullishness of 1.75, which is higher then 0.75):

2   2011-03-14  0.05  1.75

What am I doing wrong?

Answer 1

Aggregate functions like MAX() do not work the way you expect them to work.

You could use this trick to get what you want:

SELECT bullishness, stock, return2 
FROM stocks 
WHERE _date = '2011-03-14'
ORDER BY bullishness DESC
LIMIT 1

You are basically ordering by bullishness descending, so the biggest bullishness comes first. Then you are limiting to only one result.