Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: "complicated" group by select statement

    Hi, I'm using mysql 5.

    I have a table called "my_table" (it has columns called "id", "name", "url", "date_of_action")

    here's a sample of some data that might be in the table

    1, a, abc.com/adf.html, 2009-08-01
    2, a, abc.com/wer.html, 2009-07-18
    3, a, abc.com/waw.html, 2008-11-18
    4, b, nnn.com/uio.html, 2009-08-11
    5, b, nnn.com/art.html, 2007-03-19
    6, c, 123.com/dew.html, 2003-12-01

    I'd like to write a view that selects every unique "name" from this table along with the most recent date associated with each name and the url associated with each "most recent date"

    I tried this (see below), but it doesn't work quite right (it seems to pick the first url as opposed to the url that's listed in the maximum date_of_action row)

    select max(date_of_action), name, url from my_table group by name

    any thoughts on how to do this?

    tia!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.id
         , t.name
         , t.url
         , t.date_of_action
      FROM ( SELECT name
                  , MAX(date_of_action) AS last_date
               FROM my_table
             GROUP
                 BY name ) AS m
    INNER
      JOIN my_table AS t
        ON t.name = m.name
       AND t.date_of_action = m.last_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    thanks very much!

Posting Permissions

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