var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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?
FROM ( SELECT name
, MAX(date_of_action) AS last_date
BY name ) AS m
JOIN my_table AS t
ON t.name = m.name
AND t.date_of_action = m.last_date