Reputation: 369
I have the following table called uc_likes
CREATE TABLE `uc_likes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` int(255) NOT NULL,
`dwable` int(255) NOT NULL,
`actionby` int(255) NOT NULL,
`actionto` int(255) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=257 ;
INSERT INTO `uc_likes` (`id`, `type`, `dwable`, `actionby`, `actionto`, `time`) VALUES
(38, 1, 309, 4, 1, '2016-09-12 13:04:01'),
(41, 1, 372, 4, 1, '2016-09-15 13:04:07'),
(42, 1, 373, 4, 1, '2016-09-10 13:04:08'),
(55, 1, 416, 4, 1, '2016-09-15 13:04:09'),
(253, 1, 307, 5, 1, '2016-09-15 13:04:12'),
(256, 1, 372, 5, 1, '2016-09-15 13:04:13');
SELECT * FROM uc_likes;
+-----+------+--------+----------+----------+---------------------+
| id | type | dwable | actionby | actionto | time |
+-----+------+--------+----------+----------+---------------------+
| 38 | 1 | 309 | 4 | 1 | 2016-09-12 13:04:01 |
| 41 | 1 | 372 | 4 | 1 | 2016-09-15 13:04:07 |
| 42 | 1 | 373 | 4 | 1 | 2016-09-10 13:04:08 |
| 55 | 1 | 416 | 4 | 1 | 2016-09-15 13:04:09 |
| 253 | 1 | 307 | 5 | 1 | 2016-09-15 13:04:12 |
| 256 | 1 | 372 | 5 | 1 | 2016-09-15 13:04:13 |
+-----+------+--------+----------+----------+---------------------+
http://sqlfiddle.com/#!9/62794a
I'm trying to get
+-----+------+--------+----------+----------+---------------------+
| id | type | dwable | actionby | actionto | time |
+-----+------+--------+----------+----------+---------------------+
| 256 | 1 | 372 | 5 | 1 | 2016-09-15 13:04:13 |
| 253 | 1 | 307 | 5 | 1 | 2016-09-15 13:04:12 |
| 55 | 1 | 416 | 4 | 1 | 2016-09-15 13:04:09 |
| 38 | 1 | 309 | 4 | 1 | 2016-09-12 13:04:01 |
| 42 | 1 | 373 | 4 | 1 | 2016-09-10 13:04:08 |
+-----+------+--------+----------+----------+---------------------+
This omits the id 41
row as it already has a later entry with the same dwable value within the same day period (the 15th). Likewise hypothetically if the id 41
row had a timestamp on the 14th for example it should be included.
As other answers have suggested I've tried using a subquery to order before the grouping as follows:
SELECT * FROM
(SELECT * FROM `uc_likes` WHERE `actionto` = 1 ORDER BY time DESC)
AS t GROUP BY DAY(time), dwable order by time DESC;
However this still picks id 41
over id 256
. See result below:
+-----+------+--------+----------+----------+---------------------+
| id | type | dwable | actionby | actionto | time |
+-----+------+--------+----------+----------+---------------------+
| 256 | 1 | 372 | 5 | 1 | 2016-09-15 13:04:13 |
| 253 | 1 | 307 | 5 | 1 | 2016-09-15 13:04:12 |
| 55 | 1 | 416 | 4 | 1 | 2016-09-15 13:04:09 |
| 38 | 1 | 309 | 4 | 1 | 2016-09-12 13:04:01 |
| 42 | 1 | 373 | 4 | 1 | 2016-09-10 13:04:08 |
+-----+------+--------+----------+----------+---------------------+
Upvotes: 1
Views: 28
Reputation: 33945
E.g.:
SELECT x.*
FROM uc_likes x
JOIN
( SELECT dwable
, DATE(time) dt
, MAX(time) time
FROM uc_likes
GROUP
BY dwable
, dt
) y
ON y.dwable = x.dwable
AND y.dt = DATE(x.time)
AND y.time = x.time;
+-----+------+--------+----------+----------+---------------------+
| id | type | dwable | actionby | actionto | time |
+-----+------+--------+----------+----------+---------------------+
| 38 | 1 | 309 | 4 | 1 | 2016-09-12 13:04:01 |
| 42 | 1 | 373 | 4 | 1 | 2016-09-10 13:04:08 |
| 55 | 1 | 416 | 4 | 1 | 2016-09-15 13:04:09 |
| 253 | 1 | 307 | 5 | 1 | 2016-09-15 13:04:12 |
| 256 | 1 | 372 | 5 | 1 | 2016-09-15 13:04:13 |
+-----+------+--------+----------+----------+---------------------+
Upvotes: 1