Controlling rolled up rows in GROUP'ed BY queries
Posted: Mon Jan 19, 2009 8:23 am
I'm trying to control which row a GROUP BY clause will return, and have been struggling with it way too much. I probably forgot something super basic, but I just can't put my finger on it.
Consider the following schema and some sample rows:
I want to retrieve rows from attachments, GROUP'ed by their parent_id but the rows should be the latest for each parent_id (ie max(added) ). No matter how much I tried, I could not control the rows that that is rolled up by the GROUP BY.
A naive query to attempt that would be:
This returns:
Which are not the latest rows. So the order clause has no affect on the results of the grouping. In the end I came up with this gnarly query:
Which uses a dependent subquery to get the job done. This returns:
If anyone has a better idea on how to achieve this I would be grateful.
Consider the following schema and some sample rows:
Code: Select all
CREATE TABLE `attachments` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`parent_id` int(10) NOT NULL,
`parent_type` tinyint(2) NOT NULL,
`user_id` int(12) NOT NULL,
`title` varchar(250) collate utf8_unicode_ci DEFAULT NULL,
`content` text collate utf8_unicode_ci,
`added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=13 ;
INSERT INTO `attachments` (`id`, `parent_id`, `parent_type`, `user_id`, `title`, `content`, `added`) VALUES
(5, 1, 1, 1, 'test1', NULL, '2008-11-12 16:51:40'),
(6, 1, 1, 1, 'test2', NULL, '2008-11-12 17:20:46'),
(7, 2, 1, 1, 'test3', NULL, '2008-11-13 15:42:01'),
(8, 2, 1, 1, 'test4', 'ddfdf', '2008-11-13 15:42:28'),
(12, 3, 1, 1, 'test5', 'bla asdc', '2008-11-15 15:35:53');A naive query to attempt that would be:
Code: Select all
SELECT `attachments`.`id` , `attachments`.`added`
FROM `attachments`
WHERE (
parent_id
IN ( 1, 2, 3 )
AND parent_type = '1'
)
GROUP BY `attachments`.`parent_id`
ORDER BY `attachments`.`added` DESCCode: Select all
id added
12 2008-11-15 15:35:53
7 2008-11-13 15:42:01
5 2008-11-12 16:51:40Code: Select all
SELECT `attachments`.`id` , `attachments`.`added`
FROM `attachments`
WHERE (
`parent_id`
IN ( 1, 2, 3 )
AND `parent_type` = '1'
AND `added` = (
SELECT MAX( added )
FROM `attachments` AS `a`
WHERE `a`.`parent_id` = `attachments`.`parent_id`
)
)
GROUP BY `attachments`.`parent_id`Code: Select all
id added
6 2008-11-12 17:20:46
8 2008-11-13 15:42:28
12 2008-11-15 15:35:53