Get row with max date from a joined table I have this schema of dossiers having 1 to N pages, and each page has a date:

CREATE TABLE dossiers 
(id INT AUTO_INCREMENT,
code VARCHAR(50),
 PRIMARY KEY (id)
) ENGINE=MyISAM; 

CREATE TABLE pages 
(id INT AUTO_INCREMENT,
name VARCHAR(100),
date DATE,
hour TIME,
dossiers_id INT,
 PRIMARY KEY (id)
) ENGINE=MyISAM; 

INSERT INTO dossiers (id, code)
VALUES (1, 'A1'),
(2, 'A2');

INSERT INTO pages (id, name, date, hour, dossiers_id)
VALUES (1, 'page1', '2018-09-01', '17:00:00', 1),
(2, 'page2', '2018-09-05', '17:00:00', 1),
(3, 'page3', '2018-09-10', '17:00:00', 1),
(4, 'page4', '2018-08-20', '17:00:00', 1),
(5, 'page1', '2018-09-01', '17:00:00', 2);


I want to select all the dossiers alongside the page with its maximum date.

This is what I tried (error): 

SELECT *
FROM dossiers d1
INNER JOIN pages ON pages.dossiers_id = d1.id 
WHERE CONCAT(pages.date, ' ', pages.time) = (SELECT MAX(GROUP_CONCAT(date, ' ', time)) FROM pages WHERE dossiers_id = d1.id GROUP BY dossiers_id)


I need to get the A1 dossier along with its "page3" page, and the A2 dossier with its "page1" page.

I'm sure it's not as twisted as I've done, but I can't figure it out.

Suggestions?

Link to the SQL Fiddle here.
A solution is to do an extra LEFT OUTER JOIN with 'newer pages', and filtering those with no newer pages:

SELECT d.*, p1.*
  FROM dossiers AS d
  INNER JOIN pages AS p1
    ON d.id = p1.dossiers_id
  LEFT OUTER JOIN pages AS p2
    ON d.id = p2.dossiers_id
   AND (p2.date &gt; p1.date OR (p2.date = p1.date AND p2.hour &gt; p1.hour))
  WHERE p2.id IS NULL


SQLFiddle