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 <peter.brawley@stripped> 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
wultsch@stripped (email/google im)
wultsch (aim)
wultsch@stripped (msn)
--
Rob Wultsch
(480)223-2566
wultsch@stripped (email/google im)
wultsch (aim)
wultsch@stripped (msn)
