MYSQL returns the maximum value for each group

Question

I have a little problem returning the maximum value of a group using mysql

these are 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 request

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  

The 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 using self-join maxima from the 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 the maximums of the date and time, so you should use one field to save the date and time for your current scheme, which 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

, , . c heck it

, :

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

, , 2, 3 , .

Answer 3

GREATEST():

SELECT GREATEST(field1, field2);

mysql , :

SELECT GREATEST(MAX(field1), MAX(field2));

, ,

Answer 4

:

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

MAX (tran) GROUP BY tran , .

Otherwise, you will obviously end up with tran = 1234, since your query retrieves only those rows where the date is MAX (date) and the time is MAX (time), but the tran value is the normal tran value. In addition, you execute the GROUP BY identifier, while for GROUP BY there must be a maximum tran value.

So, your request should be changed to something like this:

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