CODEWITHSUNDEEP
mysqldatetimegreatest-n-per-group
FixFaier
FixFaier

Reputation: 237

MySQL get last date records from multiple

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%

Upvotes: 3

Views: 18600

Answers (2)

fancyPants
fancyPants

Reputation: 51868

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 :)

Upvotes: 16

juergen d
juergen d

Reputation: 204746

select id, type, max(datetime)
from your_table
group by id, type

Upvotes: 3

Related Questions