datether datether - 1 year ago 54
MySQL Question

Show data from last updated entry in table 1, joined with information from table2

I have two tables:

rankHistory which stores the rank of a bookID against it's given keyword.
It's structure is as following:

create table rankHistory (
id int(10),
rankUpdated timestamp,
bookId varchar(10),
keyword varchar(100),
rank int(5)

Example data contained within this table :

INSERT INTO rankHistory
(`id`, `rankUpdated`,`bookId`,`keyword`,`rank`)
(1, '2016-05-27 05:11:44', 'BK-001','romantic','80'),
(2, '2016-05-28 08:11:44', 'BK-001','romantic','15'),
(3, '2016-05-28 09:11:44', 'BK-001','romantic','13'),
(4, '2016-05-27 07:11:44', 'BK-001','romantic book','77'),
(5, '2016-05-28 08:11:44', 'BK-001','romantic book','25'),
(6, '2016-05-28 09:11:44', 'BK-001','romantic book','22'),
(7, '2016-05-27 05:11:44', 'BK-002','horror','65'),
(8, '2016-05-28 08:11:44', 'BK-002','horror','10'),
(9, '2016-05-29 07:11:44', 'BK-002','horror book','25'),
(10, '2016-05-29 08:11:44', 'BK-002','horror book','9')

bookConfig contains information of the bookID with it's given keyword.
It's structure is as following:

create table bookConfig (
id int(10),
category varchar(40),
author varchar(255),
info varchar (255)

Example data contained within this table :

INSERT INTO bookConfig
(`id`,`bookId`,`keyword`, `category`, `author`,`info`)
(1, 'BK-001', 'romantic', 'Romance', 'Author1','Romance themed book written by author1'),
(2, 'BK-001', 'romantic book', 'Romance', 'Author1','Romance themed book written by author1'),
(3, 'BK-002', 'horror ', 'Horror', 'Author2','Horror themed book written by author2'),
(4, 'BK-002', 'horror book', 'Horror', 'Author2','Horror book written by author2')

I have consolidated this in!9/9502a9.

The table rankHistory contains tens of thousands records similar as provided data and my concern is also with speed. The structure is probably wrong from the beginning but it's a legacy I have to work with unfortunately.

What I would like to achieve is the following :

Select the entry with the latest timestamp for each unique bookid/keyword combination and then join the information related to that combination contained in bookConfig.

So the desired output would be :

rankUpdated bookId keyword rank,category,author, info
2016-05-28 09:11:44, BK-001, romantic, 13, romance, Author1, Romance themed book..
2016-05-28 09:11:44, BK-001, romantic book, 22, romance, Author1, Romance themed book..
2016-05-28 08:11:44, BK-002, horror, 10, horror, Author2, Horror themed book..
2016-05-29 08:11:44, BK-002, horror book, 9, horror, Author2, Horror book ...

I have tried several ways to accomplish this without success and am lost of how to achieve this properly. I would be very thankful if someone more versed in mySQL can point me out the best way to achieve this .

Many thanks in advance for sharing your expertise

Answer Source

One option is to do a series of joins to achieve what you want:

SELECT COALESCE(t2.rankUpdated, 'NA'), AS rankUpdated, t1.bookId, t1.keyword,
    COALESCE(t2.rank, 'NA') AS rank, t1.category,,
FROM bookConfig t1
    SELECT r1.bookId, r1.rankUpdated, r1.rank, r1.keyword
    FROM rankHistory r1
        SELECT bookId, keyword, MAX(rankUpdated) AS rankUpdated
        FROM rankHistory
        GROUP BY bookId, keyword
    ) r2
        ON r1.bookId = r2.bookId AND r1.keyword = r2.keyword AND
           r1.rankUpdated = r2.rankUpdated
) t2
    ON t1.bookId = t2.bookId AND t1.keyword = t2.keyword

Follow the link below for a running demo: