MySQL Query Help MAX(DATE)

Discussion in 'Web Design and Development' started by ipodtouch2008, Jun 22, 2009.

  1. ipodtouch2008 macrumors newbie

    Joined:
    Feb 27, 2008
    #1
    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
     
  2. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #2
    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.
     
  3. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #3
    I think your group by needs to be,
    Code:
    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.
     
  4. ipodtouch2008 thread starter macrumors newbie

    Joined:
    Feb 27, 2008
    #4
    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
     
  5. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #5
    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.
     
  6. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #6
    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.
    Code:
    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.
     
  7. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #7
    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
     
  8. ipodtouch2008 thread starter macrumors newbie

    Joined:
    Feb 27, 2008
    #8
    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
     
  9. angryboffin macrumors newbie

    Joined:
    May 7, 2008
    Location:
    London
    #9
    I think this will work

    Hello ipodtouch2008,

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

    Code:
    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
     
  10. ipodtouch2008 thread starter macrumors newbie

    Joined:
    Feb 27, 2008
    #10
    /* 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;
     
  11. angryboffin macrumors newbie

    Joined:
    May 7, 2008
    Location:
    London
    #11
    Always always always test. Even when you can't.

    Replaced:

    Code:
      GROUP BY
          books.id AS book_id,
          books.book_title,
    With:

    Code:
      GROUP BY
          books.id,
          books.book_title
    How about now?

    Regards,

    Joshua

    Code:
    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
    
     
  12. ipodtouch2008 thread starter macrumors newbie

    Joined:
    Feb 27, 2008
    #12
    This work's! :)

    Thanks Everyone for your help!
     
  13. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #13
    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,
     
  14. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #14
    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
     
  15. angryboffin macrumors newbie

    Joined:
    May 7, 2008
    Location:
    London
    #15
    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:

    Code:
    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
     

Share This Page