Simon El Simon El - 26 days ago 9
MySQL Question

MySQL - select rows under an ID, group by column value that has the latest timestamp

Table:

----------------------------------------------------
ID | field_name | field_value | timestamp
----------------------------------------------------
2 | postcode | LS1 | 2016-11-09 16:45:15
2 | age | 34 | 2016-11-09 16:45:22
2 | job | Scientist | 2016-11-09 16:45:27
2 | age | 38 | 2016-11-09 16:46:40
7 | postcode | LS5 | 2016-11-09 16:47:05
7 | age | 24 | 2016-11-09 16:47:44


I wonder if anyone could give me a few pointers, based on the above data, I would like to query by ID 2, return a row for each unique field_name (if more than one row exists under the same id with the same field_name then just return the row with the latest timestamp).

I have managed to almost achieve this by grouping the field_name, which will return a list of unique rows but not necessarily the latest row.

SELECT * FROM fragment WHERE (id = :id) GROUP BY field_name


I would really be grateful for any pointers on what exactly I should do here, and how I could fit something along the lines of MAX(timestamp) in this query,

Many thanks!

Answer

Consider you first need a set of data for each ID, FieldName with the max time stamp. (generate that set) as an inline view (B below). Then, join this set (B) back to your base set allowing the inner join to eliminate the unwanted rows.

SELECT A.ID, A.field_name, A.field_value, A.timestamp
FROM Table A
INNER JOIN (SELECT ID, field_name, MAX(timestamp) TS 
            FROM table 
            GROUP BY ID, field_name) B
 on A.ID = B.ID
and A.field_name = B.field_name
and A.timestamp = B.TS

Outside of MySQL this could be done using window/analytical functions as you would be able to assign a row number to each record and eliminate those > 1 something like....

SELECT B.* 
FROM (SELECT A.ID
           , A.field_name
           , A.field_Vale
           , A.timestamp
           , Rownumber() over (Order by A.timestamp Desc) RN
      FROM Table A ) B
WHERE B.RN = 1

or using a cross apply with a limit or top.