Ben Ben - 2 months ago 6
MySQL Question

MySQL order by before group by DAY(timestamp)

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 |
+-----+------+--------+----------+----------+---------------------+

Answer

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 |
+-----+------+--------+----------+----------+---------------------+
Comments