Getting most recent row per group in MySQL

Question

I am trying to get all most recent Paper.material's for each Book.isbn. I am using left to get the base of each Book.isbn, since they can have a suffix that indicates the revision, for example : 'X1726748384Z1' or 'X1837943875Z2' etc.. I tried over partition by but it didn't work because MySQL 5.7 doesn't support this. How can I make this work?

GOAL

For each Book.isbn as X:

select left(Book.isbn, 11) BaseISBN, Paper.material, Book.date
from Book
  join Page on Book.id = Page.book_id
  join Paper on Page.id = Paper.page_id
where Book.name = 'world'
  and left(Book.isbn, 11) = X  <--
  and Page.name = 'test'
order by Book.date desc
limit 1

QUERY

select left(Book.isbn, 11) BaseISBN, Paper.material, max(Book.date)
from Book
  join Page on Book.id = Page.book_id
  join Paper on Page.id = Paper.page_id
where Book.name = 'world'
  and left(Book.isbn, 11) in('X1726748384', 'X1837943875')
  and Page.name = 'test'
group by left(Book.isbn, 11);

RETURNS

| Book.isbn   | Paper.material | max(Book.date)       |
|-------------|----------------|----------------------|
| X1726748384 | 10134248300B   | 2018-01-01T00:00:00Z |
| X1837943875 | 10985782343F   | 2021-01-01T00:00:00Z |
etc...

SHOULD RETURN

| Book.isbn   | Paper.material | max(Book.date)       |
|-------------|----------------|----------------------|
| X1726748384 | 10985782343E   | 2018-01-01T00:00:00Z |
| X1837943875 | 10985782343H   | 2021-01-01T00:00:00Z |
etc...

SQL FIDDLE

And DDLs of same:

CREATE TABLE `Book` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `isbn` varchar(255),
    `name` varchar(255),
    `date` DATETIME,
    PRIMARY KEY (`id`)
);

CREATE TABLE `Page` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` varchar(255),
    `book_id` INT NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE `Paper` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `material` varchar(255),
    `page_id` INT NOT NULL,
    PRIMARY KEY (`id`)
);

ALTER TABLE `Page` ADD CONSTRAINT `Page_fk0` FOREIGN KEY (`book_id`) REFERENCES `Book`(`id`);
ALTER TABLE `Paper` ADD CONSTRAINT `Paper_fk0` FOREIGN KEY (`page_id`) REFERENCES `Page`(`id`);

INSERT INTO `Book` (`isbn`, `name`, `date`) VALUES 
('X1234234403', 'hello', '2016-01-01'),
('X1726748384', 'world', '2017-01-01'),
('X1726748384Z1', 'world', '2018-01-01'),
('X1837943875', 'world', '2019-01-01'),
('X1837943875Z1', 'world', '2020-01-01'),
('X1837943875Z2', 'world', '2021-01-01');

INSERT INTO `Page` (`name`, `book_id`) VALUES 
('bla', 1),
('test', 2),
('test', 3),
('test', 4),
('test', 5),
('test', 6);

INSERT INTO `Paper` (`material`, `page_id`) VALUES 
('10134248300A', 1),
('10134248300B', 2), 
('10134248300C', 2), 
('10985782343D', 3), 
('10985782343E', 3), 
('10985782343F', 4), 
('10985782343G', 5), 
('10985782343H', 6);

@Strawberry, I checked that question before I submitted this, but doing that didn't work here... — konichiwa, Jun 07 '19 at 14:41
Ok, I didn't know DB fiddle's existed. That's awesome. Currently making one. Thanks — konichiwa, Jun 07 '19 at 15:06

score 1 · Accepted Answer · answered Jun 08 '19 at 23:20

'Etc.' is not very useful when trying to understand what should and shouldn't be included in a result.

Nevertheless, perhaps you're after something a bit like this...

SELECT b.*
     , z.material
  FROM book b
  JOIN
     ( SELECT LEFT(isbn,11) isbn11
            , MAX(date) date 
         FROM book 
        WHERE 
            ( isbn LIKE 'X1726748384%' 
           OR isbn LIKE 'X1837943875%' 
            ) 
          AND name = 'world'
        GROUP 
           BY isbn11
     ) x
    ON x.isbn11 = LEFT(b.isbn,11)
   AND x.date = b.date
  JOIN page y
    ON y.book_id = b.id
  JOIN paper z
    ON z.page_id = y.id
 WHERE y.name = 'test';

+----+---------------+-------+---------------------+--------------+
| id | isbn          | name  | date                | material     |
+----+---------------+-------+---------------------+--------------+
|  3 | X1726748384Z1 | world | 2018-01-01 00:00:00 | 10985782343D |
|  3 | X1726748384Z1 | world | 2018-01-01 00:00:00 | 10985782343E |
|  6 | X1837943875Z2 | world | 2021-01-01 00:00:00 | 10985782343H |
+----+---------------+-------+---------------------+--------------+
3 rows in set (0.00 sec)

Replacing `select b.*, z.material` with `select x.isbn11, z.material, x.date ....... group by x.isbn11` did the job, thanks a million! — konichiwa, Jun 09 '19 at 11:20
That doesn't sound right, but again, without seeing the whole thing... — Strawberry, Jun 09 '19 at 11:39

Getting most recent row per group in MySQL

1 Answers1