Mysql – Get row with max date from a joined table

greatest-n-per-groupjoin;MySQL

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.

Best Answer

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 > p1.date OR (p2.date = p1.date AND p2.hour > p1.hour))
  WHERE p2.id IS NULL

Related Solutions

Mysql – Are two indexes needed

An index can seek by a subset of characters, as long as you're searching from the left. E.g., "Inter%" can seek, "%net" will not.

However, the first character is not necessarily the character under which the article would be sorted. "The Internet" should go under "I", not "T". You probably need two fields, DisplayTitle and SortTitle; a single-character index on the latter may be worthwhile, but most likely a full-length index will be just fine.

Indexes are typically B-trees, and a seek will jump to the right location about equally quickly whether you have 10 or 100 entries per page. Scans are another matter, but I'd start with the simplest solution and add an extra index only if performance proves inadequate in practice.

MYSQL How to query to show data in separate columns from one field

ORACLE, NOT MYSQL

CREATE TABLE MYTABLE ( item_id int PRIMARY KEY, item_name VARCHAR(20) ); 

INSERT INTO MYTABLE (item_id,item_name) VALUES (1,'Fred'); 

INSERT INTO MYTABLE (item_id,item_name) VALUES (2,'Arthur');

INSERT INTO MYTABLE (item_id,item_name) VALUES (3,'Jane');

select * from mytable;

**ITEM_ID ITEM_NAME**           
---------- --------------------
         1 Fred                
         2 Arthur              
         3 Jane                

3 rows selected.


select 
  item_id, 
  (select unique item_name
     from MYTABLE
     where item_name='Fred') as fred,  
  (select unique item_name
     from MYTABLE
     where item_name='Arthur') as Arthur,
  (select unique item_name
     from MYTABLE
     where item_name='Jane') as Jane
from MYTABLE;
/

   ITEM_ID FRED                 ARTHUR               JANE                
---------- -------------------- -------------------- --------------------
         1 Fred                 Arthur               Jane                
         2 Fred                 Arthur               Jane                
         3 Fred                 Arthur               Jane                

3 rows selected.

I am only learning but this works, remember subquery must return only one row, otherwise there will be error.

One more, this should work faster:

select 
 item_id,
 decode(item_name,'Fred',1,0) as Fred,
 decode(item_name,'Arthur',1,0) as arthur,
 decode(item_name,'Jane',1,0) as jane
from MYTABLE;

  ITEM_ID       FRED      ARTHUR        JANE
---------- ---------- ---------- ----------
         1          1          0          0
         2          0          1          0
         3          0          0          1

3 rows selected.

Hope this helps.

Related Question