MySQL Query Help MAX(DATE)

[ipodtouch2008]

I have 2 tables `books` and `books_details`. I want to look up results from the `books` table and only get the correlating `books_details` result that has the highest date. I can't work out the MySQL query. Any help would be great. Thanks!


{table `books`}
[id] [book_title]
01 Little Red Riding Hood
02 Tom's Adventure
03 Baby Boo

{table `books_details`}
[id] [content_id] [content] [date]
01 01 1st version 20-01-2009
02 01 2nd version 22-01-2009
03 01 3rd version 25-01-2009
04 02 1st version 22-01-2009
05 02 2nd version 24-01-2009
06 02 3rd version 25-01-2009
07 02 4th version 27-01-2009
08 02 5th version 28-01-2009
09 03 1st version 22-01-2009
10 03 2nd version 27-01-2009

{** Non working MySQL }
SELECT `book_details`.`id`, `books_details`.`content_id`, `books`.`book_title`, `book_details`.`content`, MAX(`books_details`.`date`)
FROM `books`, `books_details`
WHERE `books`.`id` = `books_details`.`id`
GROUP BY `books_details`.`content_id`

{** Current Results}
01 01 Little Red Riding Hood 1st version 25-01-2009
04 02 Tom's Adventure 1st version 28-01-2009
09 03 Baby Boo 1st version 27-01-2009

At the moment it just groups the records and only gets the max date and not the row associated with the max date.

{** Wanted Results }
03 01 Little Red Riding Hood 3rd version 25-01-2009
08 02 Tom's Adventure 5th version 28-01-2009
10 03 Baby Boo 2nd version 27-01-2009

[SrWebDeveloper]

For MySQL 5x:

select b.id,b.book_title,bd.content_id,bd.content,bd.date
from (books b)
left join books_details bd on (b.id=bd.content_id)
order by bd.date desc
limit 1

Older MySQL:

select b.id,b.book_title,bd.content_id,bd.content,bd.date
from books b, books_details bd
where b.id=bd.content_id
order by bd.date desc
limit 1

Untested, wrote quickly, let me know.

[angelwatt]

I think your group by needs to be,
GROUP BY `books`.`id`
The reason being is that books_details.id is unique so grouping by it creates just as many groups. You could alternatively use books_details.content_id. You might also need to use a left join as Jim suggests. I don't use MySQL often so those skills aren't so fresh.

[ipodtouch2008]

I tried the LEFT JOIN it didn't work. I have updated query with the fixed GROUP BY field but it still gets the first rows in the GROUP BY but they have the most recent date. I want the row associated with the max(date) of each item in the GROUP BY.

I believe I need to make a sub query, but don't know how the write it.

Thanks

[angelwatt]

Looking a little closer I think the issue may reside in your date format. First, are you using the date type for the field. Second, I believe you need to use the YYYY-MM-DD format rather than your DD-MM-YYYY format. I'm still looking at other potentials issues.

[angelwatt]

I found some working queries that give you the desired result set. It involves two queries (plus one delete at the start for testing). It works for me anyhow.
drop table if exists tmp;

create table tmp select content_id, max(date) as maxdate
from books_details
group by content_id;

select books_details.id, books_details.content_id, books.book_title, books_details.content, books_details.date
from books_details, tmp, books
where books_details.content_id = tmp.content_id and
books_details.date = tmp.maxdate and
books.id = books_details.content_id
order by books_details.id
It's possible this could be simplified, but it does work and it's time for bed. Good luck. Oh, and I used the different date format when testing, YYYY-MM-DD.

[SrWebDeveloper]

I tried the LEFT JOIN it didn't work. I have updated query with the fixed GROUP BY field but it still gets the first rows in the GROUP BY but they have the most recent date. I want the row associated with the max(date) of each item in the GROUP BY.

I believe I need to make a sub query, but don't know how the write it.

Thanks

I stand by my original query based on your request. Please explain what didn't work, i.e. specific error, results returned, and MySQL version number.

As to date, you should be using one of 3 formats:

DATE
A date
The supported range is ‘1000-01-01’ to ‘9999-12-31’. MySQL displays DATE values in ‘YYYY-MM-DD’ format
DATETIME
A date and time combination
The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. MySQL displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format
INT(10) (store unix timestamp)

-jim

[ipodtouch2008]

Hey SrWebDeveloper,

My dates in post are wrong - my actual dates and in MySQL Timestamp format.

MySQL Server v 5.0.67

Thanks

The query only resulted in one result.
----------------
3 baby boo 3 2nd version 2009-06-23 14:10:07
----------------
Or with out the limit (just the results in reverse date order)
----------------
3 baby boo 3 2nd version 2009-06-23 14:10:07
3 baby boo 3 1st version 2009-06-23 14:09:56
2 tom's adventure 2 5th version 2009-06-23 14:09:25
2 tom's adventure 2 4th verison 2009-06-23 14:09:13
2 tom's adventure 2 3rd version 2009-06-23 14:09:01
2 tom's adventure 2 2nd verison 2009-06-23 14:08:48
2 tom's adventure 2 1st version 2009-06-23 14:08:34
1 red riding hood 1 3rd version 2009-06-23 14:06:51
1 red riding hood 1 4th version 2009-06-23 14:06:51
1 red riding hood 1 2nd version 2009-06-23 14:06:46

[angryboffin]

Hello ipodtouch2008,

I think that this is what you want (it's generic SQL but should work on mySQL):

SELECT
latest_detail_dates.book_id,
latest_detail_dates.book_title,
latest_detail_dates.latest_detail_date,
latest_book_details.content AS latest_detail_date_content
FROM
(
SELECT
books.id AS book_id,
books.book_title,
max(books_details.date) AS latest_detail_date
FROM
books
INNER JOIN
book_details
ON
books.id = book_details.content_id
GROUP BY
books.id AS book_id,
books.book_title,
) AS latest_detail_dates
INNER JOIN
books_details AS latest_book_details
ON
latest_book_details.content_id = latest_detail_dates.book_id
AND latest_book_details.date = latest_detail_dates.latest_detail_date


Notes:
I've joined on books.id = book_details.content_id , not book_details.id , as the id is unique on the example books_details table.
This code will produce multiple lines for the same book if it has two or more book_details with the same maximum date.
Whatever date format you use should not matter; mySQL ought to still be able to sort them properly for the maximum date.

Assumptions:
Assuming that outer joins aren't necessary. If they are, then convert both INNER JOINS to LEFT OUTER JOINS.

Hopefully that SQL will run; it's posted untested.

I shall now wave at SrWebDeveloper. How are you doing? I lost that race against time I was telling you about earlier.

Regards,

Joshua

[ipodtouch2008]

Hello ipodtouch2008,

I think that this is what you want (it's generic SQL but should work on mySQL):

SELECT
latest_detail_dates.book_id,
latest_detail_dates.book_title,
latest_detail_dates.latest_detail_date,
latest_book_details.content AS latest_detail_date_content
FROM
(
SELECT
books.id AS book_id,
books.book_title,
max(books_details.date) AS latest_detail_date
FROM
books
INNER JOIN
book_details
ON
books.id = book_details.content_id
GROUP BY
books.id AS book_id,
books.book_title,
) AS latest_detail_dates
INNER JOIN
books_details AS latest_book_details
ON
latest_book_details.content_id = latest_detail_dates.book_id
AND latest_book_details.date = latest_detail_dates.latest_detail_date


Notes:
I've joined on books.id = book_details.content_id , not book_details.id , as the id is unique on the example books_details table.
This code will produce multiple lines for the same book if it has two or more book_details with the same maximum date.
Whatever date format you use should not matter; mySQL ought to still be able to sort them properly for the maximum date.

Assumptions:
Assuming that outer joins aren't necessary. If they are, then convert both INNER JOINS to LEFT OUTER JOINS.

Hopefully that SQL will run; it's posted untested.

I shall now wave at SrWebDeveloper. How are you doing? I lost that race against time I was telling you about earlier.

Regards,

Joshua

/* MySQL 16:14:44 */ You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS book_id, books.book_title, ) AS latest_detail_dates INNER JOIN bo' at line 1;

[angryboffin]

Always always always test. Even when you can't.

Replaced:

GROUP BY
books.id AS book_id,
books.book_title,

With:

GROUP BY
books.id,
books.book_title

How about now?

Regards,

Joshua


SELECT
latest_detail_dates.book_id,
latest_detail_dates.book_title,
latest_detail_dates.latest_detail_date,
latest_book_details.content AS latest_detail_date_content
FROM
(
SELECT
books.id AS book_id,
books.book_title,
max(books_details.date) AS latest_detail_date
FROM
books
INNER JOIN
book_details
ON
books.id = book_details.content_id
GROUP BY
books.id,
books.book_title
) AS latest_detail_dates
INNER JOIN
books_details AS latest_book_details
ON
latest_book_details.content_id = latest_detail_dates.book_id
AND latest_book_details.date = latest_detail_dates.latest_detail_date

[ipodtouch2008]

Always always always test. Even when you can't.

Replaced:

GROUP BY
books.id AS book_id,
books.book_title,

With:

GROUP BY
books.id,
books.book_title

How about now?

Regards,

Joshua


SELECT
latest_detail_dates.book_id,
latest_detail_dates.book_title,
latest_detail_dates.latest_detail_date,
latest_book_details.content AS latest_detail_date_content
FROM
(
SELECT
books.id AS book_id,
books.book_title,
max(books_details.date) AS latest_detail_date
FROM
books
INNER JOIN
book_details
ON
books.id = book_details.content_id
GROUP BY
books.id,
books.book_title
) AS latest_detail_dates
INNER JOIN
books_details AS latest_book_details
ON
latest_book_details.content_id = latest_detail_dates.book_id
AND latest_book_details.date = latest_detail_dates.latest_detail_date


This work's! :)

Thanks Everyone for your help!

[angelwatt]

I stand by my original query based on your request. Please explain what didn't work,

I think you missed that he wanted the max date per group, so there would be 3 results, not just one like the query you gave.

@OP
Did you try the queries I gave you, they worked for me, or did you want to keep it to one query? Though mine isn't much longer than the other solution you now have,

[SrWebDeveloper]

I think you missed that he wanted the max date per group, so there would be 3 results, not just one like the query you gave.


Turns out my query did work just as the OP requested, because in his original response they said, "and only get the correlating `books_details` result that has the highest date." Being the word "result" is singular, which is what I keyed on. Often users post code that does one thing (i.e. "group by" when not needed for a single result) and mean another in their question. It's cool the thread made clear after a few posts the correct query. But I didn't "miss" a thing at the time I posted. I'm sorry if this sounds defensive, and hope folks following this know when and when not to use sub-queries and groupings, that's the good of it.

-jim

[angryboffin]

I would agree with SrWebDeveloper that the original request could be read to mean that ipodtouch2008 was only interested in the latest book details of one book at the time. In that case, his query of:

SELECT
books.id, books.book_title, book_details.content_id,
book_details.content, book_details.date
FROM
books
LEFT OUTER JOIN
book_details
ON
books.id = book_details.content_id
WHERE
books.id = ???
ORDER BY
book_details.date DESC
LIMIT 1

is not only correct (assuming {content_id, date} is unique in the book_details table), but is also very likely to perform better than my query.

Regards,

Joshua