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