Ben
Ben

Reputation: 369

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

Upvotes: 1

Views: 28

Answers (1)

Strawberry
Strawberry

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

Related Questions