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]
