"complicated" group by select statement

lopiparo · #1 (**permalink**) 07-16-09, 14:04

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!

r937 · #2 (**permalink**) 07-16-09, 14:26

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

lopiparo · #3 (**permalink**) 07-16-09, 15:42

thanks very much!

Internet Services

Web Development

Digital Marketing

Consumer Tech