Explore our sites

Stack Exchange
tour help
Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Consider following table.

enter image description here

I'm trying to write a query to display - Max values for all the parts per category. Also display the date when the value was max.

So i tried this -

select Part_id, Category, max(Value), Time_Captured
from data_table
where category = 'Temperature'
group by Part_id, Category

First of all, mysql didn't throw an error for not having Time_Captured in group by. Not sure if its a problem with mysql or my mysql.

So I assume it should return -

1   Temperature 50  11-08-2011 08:00
2   Temperature 70  11-08-2011 09:00

But its returning me the time captured from the first record of the data set i.e. 11-08-2011 07:00

Not sure where I'm going wrong. Any thoughts?

(Note: I'm running this inside a VM. Just in case if it changes anything)

share|improve this question
add comment

1 Answer

up vote 0 down vote

You need to join to the results of a query that finds the max(value), like this:

select dt.Part_id, dt.Category, dt.Value, dt.Time_Captured
from data_table dt
join (select Part_id, Category, max(Value) as Value
          from data_table group by 1, 2) x 
    on x.Part_id = dt.Part_id and x.Category = dt.Category
where dt.category = 'Temperature';

Note that this will return multiple rows if there are multiple rows with the same max value.

If you want to limit this to one row even though there are multiple matches for max(value), select the max(Time_Captured) (or min(Time_Captured) if you prefer), like this:

select dt.Part_id, dt.Category, dt.Value, max(dt.Time_Captured) as Time_Captured
from data_table dt
join (select Part_id, Category, max(Value) as Value
          from data_table group by 1, 2) x 
    on x.Part_id = dt.Part_id and x.Category = dt.Category
where dt.category = 'Temperature'
group by 1, 2, 3;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.