I have the following tables:
CREATE TABLE `Channels` (
`id` INT(11) AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(255) NOT NULL,
`views` INT(11) NOT NULL DEFAULT 0,
) Engine = InnoDB;
INSERT INTO Channels (title, views)
VALUES ('Movies', 50030),
('TV shows', 51201),
('Howtos', 18601);
CREATE TABLE `Videos` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`channel_id` INT(11),
`name` VARCHAR(255) NOT NULL,
`description` TEXT,
`views` INT(11) NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL,
`updated_at` DATETIME,
CONSTRAINT videos_channel_id_fk
FOREIGN KEY ( channel_id )
REFERENCES Channels ( id )
) ENGINE=InnoDB;
INSERT INTO Videos (`channel_id`, `name`, `created_at`)
VALUES (1, 'A', '2019-10-03 00:00:12'), (1, 'B', '2019-03-01 00:12:12'),
(1, 'C', '2019-10-01 19:12:12'), (1, 'D', '2019-10-03 01:55:55'),
(2, 'E', '2019-10-03 01:58:00'), (2, 'F', '2019-10-01 15:11:11'),
(2, 'G', '2019-09-09 11:11:11'), (2, 'H', '2019-08-01 12:53:10');
I'm trying to get a distinct list of channels with their most recent videos.
For example for Channel 1, it would be Video 'D' at '2019-10-03 01:55:55', for Channel 2 it would be Video 'E' at '2019-10-03 01:58:00');
I've tried doing a self join like this,
SELECT v1.id, v1.channel_id, v1.name FROM Videos AS v1
LEFT JOIN Videos AS v2 ON v1.channel_id = v2.channel_id AND v1.created_at>v2.created_at
WHERE v1.created_at IS NULL
but my Video id's never match up with the latest video. i.e. I'll end up with video id: 1 where I want Video id: 4.
Best Answer
fiddle