SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist rhysboy84's Avatar
    Join Date
    May 2007
    Location
    Colwyn Bay, North Wales, UK
    Posts
    438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Find Last Posted Message associated with each category

    Hi guys,

    I'm having trouble formulating a query. I think I'm trying to be too clever here though

    I have a table, which has an id (Primary Key, Auto Incrmement), Category (varchar), Message (varchar), Date (datetime).

    So assuming I have the following in the table:-

    Code MySQL:
    id |Category |Message      |Date*     |
    ---|---------|-------------|----------|
    1  |News     |News Item 1  |Yesterday |
    2  |Sport    |Sport Item 1 |2 Days Ago|
    3  |Music    |Music Item 1 |3 Days Ago|
    4  |Sport    |Sport Item 2 |Yesterday |
    5  |News     |News Item 2  |Today     |
    6  |Sport    |Sport Item 3 |Today     |
     
    (assuming date is the proper field, just showing this for this example)

    What I am after is a query so that it returns the most recent result in each category (so in my example it'd be id's 3,5,6).

    I've tried using distinct, but can't seem to get my head around it. I'm assuming that I can create a separate table that has a list of all the categories & matches up the category to the ID, but just wondering if it's possible using one table.

    Any suggestions on where to start? I'd like to say I'm average at MySQL, so once I have the ball rolling, I can problably help, it's just getting started that's annoying me!

    Cheers
    I'm Rhys Wynne & I blog at Winwar Media
    WP Email Capture: Free Email/Ebook Marketing Wordpress Plugin
    UK Based SEO? Tweet Your Location to #ukseohere!
    | My Brand New Brand | Twitter |

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT t.id
         , t.Category
         , t.Message     
         , t.Date
      FROM ( SELECT Category
                  , MAX(Date) AS maxdate
               FROM daTable
             GROUP
                 BY Category ) AS m
    INNER
      JOIN daTable AS t
        ON t.Category = m.Category
       AND t.Date = m.maxdate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist rhysboy84's Avatar
    Join Date
    May 2007
    Location
    Colwyn Bay, North Wales, UK
    Posts
    438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's perfect thank you
    I'm Rhys Wynne & I blog at Winwar Media
    WP Email Capture: Free Email/Ebook Marketing Wordpress Plugin
    UK Based SEO? Tweet Your Location to #ukseohere!
    | My Brand New Brand | Twitter |


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •