Group-by in MySQL: how to specify which data to show from the other columns

Question

Sorry for the ambiguous title; but I don't now how to describe it different.

I have the following table:

imei | date       | time  | some more fields
345  | 2012-06-28 | 07:18 | .....
345  | 2012-06-28 | 07:20 | .....
345  | 2012-06-28 | 07:21 | .....
987  | 2012-06-28 | 07:19 | .....
etc etc

I want to get the latest row of ervery distinct imei, so:

345  | 2012-06-28 | 07:21
987  | 2012-06-28 | 07:19

• Using SELECT * FROM t GROUP BY imei results in using the first line instead of the last one.
• Ordering by time obviously orders the result relation instead of the sub.
• Using having is only for stating a condition....

How can I write a query to do this?

Answer 1

As stated in the manual:

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

To obtain the groupwise maximum (as you want), you need to join the result of a grouped subquery with your table:

SELECT * FROM t JOIN (
  SELECT imei, MAX(ADDTIME(date, time)) AS dt FROM t GROUP BY imei
) AS u ON t.imei = u.imei AND t.date = DATE(u.dt) AND t.time = TIME(u.dt)

Answer 2

Fahim is right. Separated date and time columns make life unnecessarily difficult. No matter.

Try this.

SELECT IMEI, MAX(STR_TO_DATE(CONCAT(`date`,` `,'`time`)))
  FROM T
 GROUP BY IMEI

You may have to muck about to get the MAX/STR/CONCAT expression just right.

This is a summary query that finds the maximum datetime for each of your IMEI items.

Answer 3

Take a look at that post: How to combine date from one field with time from another field - MS SQL Server

In order to this you can simply use the SQL statement:

SELECT imei, (date+time) AS datim, [some more fields] FROM yourTable;

Then you can use max, min, distinct on the virtual field datim.

Answer 4

maybe you should order by both imei and date, time:

SELECT * FROM t GROUP BY imei ORDER BY imei, date DESC, time DESC