Skip navigation links

MySQL Forums :: Views :: Simple But Not So Simple


Advanced Search

Simple But Not So Simple
Posted by: Jamie B ()
Date: October 05, 2012 02:29AM

Hi All,

I have the following table structure machine_statuses:

p_id machine_id machine_status status_date

1 100 start 2012/10/05 90:00:00
2 101 start 2012/10/05 90:00:01
3 101 moving 2012/10/05 90:00:02
4 102 start 2012/10/05 90:00:03


I want to group the above table by machine_id and have only the LASTEST status as entered in status_date. Hence, I am doin the following SQLs, but it is not working:

select p_id, machine_id, machine_status, MAX(status_date) from machine_statuses
group by machine_id

or

select p_id, machine_id, machine_status, status_date from machine_statuses
group by machine_id
order by MAX(status_date)

or

select p_id, machine_id, machine_status, status_date from machine_statuses
group by machine_id
order by MAX(p_id)

...but I am getting incorrect results, like:

1 100 start 2012/10/05 90:00:00
2 101 start 2012/10/05 90:00:02
4 102 start 2012/10/05 90:00:03

or

1 100 start 2012/10/05 90:00:00
2 101 start 2012/10/05 90:00:01
4 102 start 2012/10/05 90:00:03


...when I would like the following:

1 100 start 2012/10/05 90:00:00
3 101 moving 2012/10/05 90:00:02
4 102 start 2012/10/05 90:00:03


...I would be most appreciative if anyone could help me solve this challenging scenario.

Many thanks,
Jamie B

Options: ReplyQuote


Subject Views Written By Posted
Simple But Not So Simple 1601 Jamie B 10/05/2012 02:29AM
Re: Simple But Not So Simple 837 Jamie B 10/05/2012 03:50AM
Re: Simple But Not So Simple 663 Peter Brawley 11/22/2012 08:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.