Aleph One wrote:
> [ Please respond to me directly as I am not part of the list ]
> Here is a little question for the group. I'd like to perform a
> certain select using a single query but I believe it is not
> possible. Maybe someone knows better than me.
> The basic idea is to perform a select using a group by clause
> to return the MAX() of a column. The difficult part is that
> I would also like at the same time to return other columns
> associated with the record whose column is equals to the MAX().
> Something like:
> SELECT MAX(A), B FROM table GROUP BY C
> Now the problem is that unless 'B' is unique within each
> GROUP BY group depending on the database server the select
> will fail with an error or return B for some random column
> within the group. MySQL is of the later type.
> I'd like for 'B' to be from the row where A = MAX(A).
> The only way I can think of doing it is using a sub-selects
> which are not supported by MySQL.
> Aleph One / aleph1@stripped
Hi Aleph One
You are right, you can't do that with one SELECT.
You could of course use a temporary table, to store the C and MAX(A) values.
With this you could do a JOIN with the original table to get what you want.
|• difficult select||Aleph One||10 Jun|
| • Re: difficult select||Christian Mack||10 Jun|