List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:September 25 2007 9:53pm
Subject:RE: Ordering by unrelated column in a GROUP BY
View as plain text  
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)
Thread
Ordering by unrelated column in a GROUP BYRob Wultsch25 Sep
  • Re: Ordering by unrelated column in a GROUP BYPeter Brawley25 Sep
    • Ugly sql optimization help?Bryan Cantwell25 Sep
RE: Ordering by unrelated column in a GROUP BYRob Wultsch25 Sep