Using ORDER BY and GROUP BY together

[Graham Roberts]

Hi,

I have a situation that can easily be solved by using many queries (thousands) or upgrading my version of MySQL. However, I don't want want to kill the database by hitting it with thousands of queries, also I cant upgrade the DB right now (4.0.16-standard).

Heres my situation: I have a table with a few hundred thousand records in it. You could think of it like stock market data, for each stock there are records of it price taken every 20 minutes throughout the day.

What I need to do is pull out a report for each stock with the best price data.

So I am grouping on stockID. But with using the GROUP BY on its own I can't actually access the rest of the fields for this records, only stockID and any aggregates. Specifying any other columns would simply give me arbitrary values based on one record from within this group.

To overcome this I figured I could also use an ORDER BY price DESC thinking this would sort the data before the grouping was done ensuring that the reported columns would be the first record in the group, which due to the ORDER BY would be the one with the best price.

But the ORDER BY doesn't actually seem to be doing this, does MySQL have a problem using ORDER BY and GROUP BY in the same query?

As a work around I am thinking of using a temporary table which will be sorted on price, then running my GROUP BY query on this temp table.

It looks like the records returned in a GROUP BY correspond to the last record making up the group, can anyone confirm this?

Sorry about the long post!

Many thanks for your help.

Graham Roberts