RE: Ordering by unrelated column in a GROUP BY
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]
Re: Ordering by unrelated column in a GROUP BY
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ordering by unrelated column in a GROUP BY
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 [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]