From: Peter Brawley
Date: September 25 2007 7:56pm
Subject: Re: Ordering by unrelated column in a GROUP BY
List-Archive: http://lists.mysql.com/mysql/209249
Message-Id: <46F96803.8000700@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

You might like to compare the performance of ...

SELECT  t1.data1, t1.data2, MAX(t1.occurrence)
FROM t1
GROUP BY data1,data1
ORDER BY occurrence;

with...

SELECT t1.data1, t1.data2,t1.occurrence
FROM t1
LEFT JOIN t1 AS t2 ON t1.data1=t2.data2 AND t1.data2=t2.data2 AND 
t1.occurrence < t2.occurrence
WHERE t2.id IS NULL
ORDER BY occurrence;

PB

-----

Rob Wultsch wrote:
> Suppose I have a table:
> CREATE TABLE `t1` (
>   `id` int(11) NOT NULL auto_increment,
>   `data1` varchar(50) ,
>   `data2` varchar(50) ,
>   `data3` varchar(50) ,
>   `occurance` datetime ,
>   PRIMARY KEY  (`id`)
> )
>
> And I want to pull the most recent entry of each set of unique
> combinations of `data1` and `data2`. Per the mysql manual:
> "If you use GROUP BY, output rows are sorted according to the GROUP BY
> columns             as if you had an ORDER BY for the same columns. To
> avoid the overhead of sorting that             GROUP BY produces, add
> ORDER BY NULL:"
>
> What is the mysql syntax (if such syntax exists) to order within a
> group by another field, in this example `occurance`?
>
> Is there a better solution to this using sub selects?
>
> Am I on crack for considering doing something like:
> CREATE TEMPORARY TABLE `tmpt1` (
>   `id` int(11) NOT NULL auto_increment,
>   `data1` varchar(50) ,
>   `data2` varchar(50) ,
>   `data3` varchar(50) ,
>   `occurance` datetime ,
>   PRIMARY KEY  (`id`),
>   UNIQUE(`data1`,`data2`)
> ) ;
>
> INSERT IGNORE INTO `tmpt1`(
> `id`,
> `data1`,
> `data2`,
> `data3`,
> `occurance`
> ) SELECT *
> FROM `t1`
> ORDER BY `occurance` DESC
>
>