Re: difficult select

[Christian Mack]

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.

Tschau
Christian