List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:September 25 2007 5:43pm
Subject:Ordering by unrelated column in a GROUP BY
View as plain text  
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
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