Help with a select query

[kyklops]

Hi everyone,
I'm new to mySQL. I have a table like this one:

+-------+-------+-------+
| date  | plant | value |
+-------+-------+-------+
| date1 |  1    |  10   |
| date2 |  1    |  12   |
| date3 |  1    |  15   |
| date4 |  2    |  30   |
| date5 |  2    |  34   |
| date3 |  2    |  43   |
+-------+-------+-------+

The result I want is "the last (more recent) value for each plant". I mean

+-------+-------+-------+
| date  | plant | value |
+-------+-------+-------+
| date3 |  1    |  15   | 
| date5 |  2    |  34   |
+-------+-------+-------+

I tried as follows:

select max(date), plant, ?value?
from table_name
group by plant;

The problem, I know, is on the 'value' column. How can I return the the proper value?
Thank you.

[smantscheff]

drop table if exists plants;
create table plants (date char(10), plant integer, value integer);
insert into plants values 
('date1','1','10'),
('date2','1','12'),
('date3','1','15'),
('date4','2','30'),
('date5','2','34'),
('date3','2','43');
select p1.date, p1.plant, value from plants p1 where p1.date = (select max(date) from plants where plant=p1.plant group by plant ) 
;

[kyklops]

Thank you!

[kartisathis]

select * from (SELECT *
FROM `plants`
WHERE 1
ORDER BY `DATE` DESC ) temp group by temp.`plant`

[smantscheff]

@karthisathis: This is a sloppy solution. It relies on the fact that in mixing aggregate and non-aggregate function mysql takes the first encountered row for filling in the non-aggregate values. It will lead to incompatibility with other SQL dialects and leads the user to invalid assumptions about the results of queries with aggregate functions.