MYSQL returning the max value per group

Question

I have a bit problem returning the max value of a group using mysql

this is my columns

     id | date           | time     | tran
--------+----------------+----------+----------
      1 | 2014/03/31     | 17:23:00 | 1234
      1 | 2014/03/31     | 17:24:00 | 3467
      2 | 2014/03/31     | 17:26:00 | 2345

My query

SELECT id, max(date), MAX(time) , tran
FROM table
GROUP BY id

RESULT

     id | date           | time     | tran
--------+----------------+----------+----------
      1 | 2014/03/31     | 17:26:00 | 1234
      2 | 2014/03/31     | 17:24:00 | 2345  

Expected answer should be

     id | date           | time     | tran
--------+----------------+----------+----------
      1 | 2014/03/31     | 17:26:00 | 3467
      2 | 2014/03/31     | 17:24:00 | 2345  

Answer 1

You can do this by using self join on the maxima from same table

SELECT t.* FROM
Table1 t
JOIN (
SELECT id, max(date) date, MAX(time) time , tran
FROM Table1
GROUP BY id) t2
ON(t.id=t2.id AND t.date=t2.date AND t.time=t2.time)

Fiddle

There may be differences between maxima of date and time so you should use a single field for saving date and time for you current schema this one is optimal

SELECT t.* FROM
Table1 t
JOIN (
SELECT id, 
  max(concat(date,' ',time)) `date_time`

FROM Table1
GROUP BY id) t2
ON(t.id=t2.id AND (concat(t.date,' ',t.time))=t2.date_time )

Fiddle

Answer 2

There is a great article on this theme which a read every time i am facing your problem. You might want to check it

Applying to you query, this will look like:

   SELECT *
   FROM `table`
   WHERE (
      SELECT count(*) FROM `table` AS t
      WHERE `t`.`id` = `table`.`id` AND `t`.`tran` <= `table`.`tran`
   ) < 2;

Best thing i like about this way, you can easily get top 2, 3 or any number of rows you need