0

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.

Edited by kyklops: n/a

3
Contributors
4
Replies
5
Views
5 Years
Discussion Span
Last Post by smantscheff
0
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 ) 
;
0
select * from (SELECT *
FROM `plants`
WHERE 1
ORDER BY `DATE` DESC ) temp group by temp.`plant`
0

@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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.