Peter, Thank you for your reply. MAX(t1.occurrence ) will pull the max of the occurrence column out of the group, but the other collumns (like data3 or id) would still be sorted by the GROUP BY.
I will try your second solution, but the tables I am working are thousands of row and your solution look to me like it would be expensive as heck. I am also considering creating a new table that would hold the values of the most recent entries. It would something like: CREATE TABLE `t2` ( `id` int(11) , `data1` varchar(50) , `data2` varchar(50) , UNIQUE KEY (`data1`,`data2`) ) which I would keep up to date with triggers and join against the previously mentioned t1. On 9/25/07, Peter Brawley <[EMAIL PROTECTED]> wrote: > 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 > > > > > -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
