mysql查询出最后日期的记录,MySQL从多个获取最后日期记录

I have the following in my db:

ID datetime type value

1094 2013-09-25 09:11:18 HDD 89%

1094 2013-09-25 09:11:18 CPU 55%

1093 2013-09-25 09:11:18 HDD 77%

1093 2013-09-25 09:11:18 CPU 55%

1094 2013-09-25 08:21:28 HDD 85%

1094 2013-09-25 08:21:28 CPU 11%

1093 2013-09-25 07:04:00 HDD 76%

I want to get the last date for every ID and type record:

1094 2013-09-25 09:11:18 HDD 89%

1094 2013-09-25 09:11:18 CPU 55%

1093 2013-09-25 09:11:18 HDD 77%

1093 2013-09-25 09:11:18 CPU 55%

解决方案

You can use GROUP BY, but it's important to understand how it works.

When you use GROUP BY a group of rows is collapsed into one and what is displayed in the other columns that are not used in your GROUP BY clause, is determined either by using aggregate functions or it is a random row out of that group. You can't be sure to get the row corresponding to the row displayed holding the MAX() value or whatever aggregate function you used. This becomes especially obvious, when you do a query like:

SELECT id, MIN(whatever), MAX(whatever), another_column

FROM your_table

GROUP BY id

The "another_column" can belong to the row holding the MIN() value or the MAX() value or even to another row not displayed at all. In other RDBMS than MySQL it's actually forbidden to select columns that do not use an aggregate function or are not listed in the GROUP BY clause, just to prevent that users make a mistake or think they got the right result.

So, in your case you seem to want to display the column "value" too. For above reasons, juergen_d's answer is wrong, because it doesn't select "value", and if it would, you can't be sure it's the right "value". Filipe Silva's answer is wrong, because it groups by "value". Doing this will in your case actually return the whole table. Romesh's answer is wrong, because using two times the MAX() function will get you the max values, obviously but not the value corresponding to the datetime value.

So, how do you have to do it? Here 3 ways are described. Learn them by applying them yourself. It's not that hard :)