Graham Roberts wrote:
> 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.
>
> ...
> 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?
No. ORDER BY is always done after GROUP BY - not only in MySQL
> 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.
That's one way. See
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html
--
felix
Please use
BBCode to format your messages in this forum.