CODEWITHSUNDEEP
sql
jou
jou

Reputation: 63

retrieve max value for the other column

I need to get only the maximum time for each machine_id. My code retrieves all times value for each machine_id. Bellow is some rows produced from my code 

machine_id  time
--------------------------
4246147567  2506135493517
1301977     2503322826186
4135091837  2498530284226
4246147567  2497077644943
4820021252  2496367903730
1301977     2495450309333

As you can see I have a different result for machine_id (4246147567, 1301977), it supposes to have the maximum time for each machine. In other words, I must have one record for each machine.

My current code is:

select distinct 
    machine_id, time 
from 
    failure_host_machine_events 
where  
    event_type = 1
-- group by machine_id, time
order by 
    time desc

Upvotes: 1

Views: 29

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

you should use max() and group by 

select    machine_id, max(time) 
from failure_host_machine_events 
where  event_type = 1
group by machine_id
order by max(time) desc

Upvotes: 2

Related Questions