A problem that I have faced with SQL many times is pre-ordering the
GROUP BY clause.
When you GROUP BY, MySQL will return a set of records which I call representing records. These records represent the group that is selected.
select id from test group by class will return one representing record per unique value of the
class field. The problem with this is that you have no control over which record will represent the group.
Lets setup an example so you can see the problem in more detail.
# # setup the table and some data # CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `class` varchar(64) DEFAULT NULL, `created` date DEFAULT NULL, PRIMARY KEY (`id`) ); INSERT INTO `test` VALUES ('1', 'aaa', '2009-01-01'), ('2', 'aaa', '2009-01-02'), ('3', 'bbb', '2009-01-03'), ('4', 'bbb', '2009-01-04'), ('5', 'ccc', '2009-01-05'), ('6', 'ccc', '2009-01-06');
Great, now we have some data that we can group. Now I want to find the latest created records, one per class. The records I need are
attempt 1 - group by
select id from test group by class
incorrect - returns the earliest created (
attempt 2 - group by order by
select id from test group by class order by created desc
incorrect - returns the earliest created, only the final result is ordered (
attempt 3 - subselect a field
select id, class class1, (select id from test where class=class1 order by created desc limit 1) id2 from test group by class
almost there - returns field id2 containing
attempt 4 - joining using subselects
select t2.id from test t1 left join test t2 on t2.id = (select id from test where class=t1.class order by created desc limit 1) group by t1.class
Success! This returns representing record in table t2 containing
Tagged with : MySQL