CODEWITHSUNDEEP
mysql
Uchsun
Uchsun

Reputation: 361

Can't Find the result

I have the following table: 

  ID | ID_MESIN |PIC         | Due Date |
________________________________________
    1 | 1081     | Hariyanto |12/12/2013
    2 | 1081     | Budi      |13/12/2013
    3 | 1059     | Jono      |23/12/2013
    4 | 1059     | Yuki      |24/12/2013

I need a query that gives the last row per ID_MESIN Column.

ex : if I run the Query with ID_Mesin = 1059 then result is 

4 | 1059     | Yuki      |24/12/2013`

if I run the Query with ID_Mesin = 1081 the result is 

2 | 1081     | Budi      |13/12/2013

I have query Like this:

 SELECT * , MAX( ID ) AS ID FROM mytable WHERE ID_MESIN = '1081'

and like this:

SELECT * FROM mytable WHERE ID_MESIN = '1081' AND  ID = (SELECT MAX(ID) FROM mytable)

But the result is wrong: 

 2 | 1081     | Hariyanto    |12/12/2013

ID and ID_MESIN is true but other column is wrong.

Upvotes: 0

Views: 27

Answers (1)

Jim Garrison
Jim Garrison

Reputation: 86774

You're close:

SELECT * FROM mytable WHERE ID_MESIN = '1081' 
AND ID = (SELECT MAX(ID) FROM mytable where ID_MESIN = '1081')

If you wanted the highest ID rows for all ID_MESIN values:

select t.* 
from mytable t
join (select id_mesin, max(id) as id from mytable group by id_mesin) m
  on t.id_mesin = m.id_mesin and t.id = m.id

Upvotes: 1

Related Questions