From: Peter Brawley
Date: February 10 2007 5:48pm
Subject: Re: grouping
List-Archive: http://lists.mysql.com/mysql/204902
Message-Id: <45CE057F.2030601@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 >select column1, column2, max(column3) as maximum
 >from table_name group by column 1;

 >Please tell me if the values from "column2" will contain the values from
 >those records where the column3 has the maximum value.

They will not.

 >please tell me how to do this as fast as possible.

What's fastest depends on your table, indexes &c. Here is one way...

select
  t1.column1,
  (select column2 from table_name t2 where t2.column1=t1.column1) as 
column2,
  max(t1.column3) as maximum
from table_name t1
group by column1;

and here is another, usually faster.

select t1.column1, t1.column2, t1.column3
from table_name t1
left join table_name t2 on t1.column1=t2.column1 and t1.column3<t2.column3
where t2.column1 is null;

PB



Octavian Rasnita wrote:
> Hi,
>
> I want to use:
>
> select column1, column2, max(column3) as maximum from table_name group 
> by column 1;
>
> Please tell me if the values from "column2" will contain the values 
> from those records where the column3 has the maximum value.
> If it doesn't, please tell me how to do this as fast as possible. I 
> know that I could get the maximum values, than make another query and 
> get the values from the lines that have that max value, but I think 
> this will take too much time.
>
> Thank you.
>
> Octavian
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.411 / Virus Database: 268.17.34/679 - Release Date: 2/10/2007