Good morning,
I am trying to get the most up to date element of a group from the database but no luck so far. Ideas anyone? I guess the solution is quite easy but I am really stuck there...
Data:
+---------------+----------+-------+------------+---------------------+
| transition_id | field_id | value | changed_by | changed |
+---------------+----------+-------+------------+---------------------+
| 3 | 1 | Data | Mike | 2018-08-13 00:00:00 |
| 3 | 2 | Data | Mike | 2018-08-13 00:00:00 |
| 3 | 3 | Data | Mike | 2018-08-13 00:00:00 |
| 3 | 1 | Data | Mike | 2018-08-20 00:00:00 |
| 4 | 1 | Data | Mike | 2018-08-15 00:00:00 |
+---------------+----------+-------+------------+---------------------+
Expected results:
+---------------+----------+-------+------------+---------------------+
| transition_id | field_id | value | changed_by | changed |
+---------------+----------+-------+------------+---------------------+
| 3 | 2 | Data | Mike | 2018-08-13 00:00:00 |
| 3 | 3 | Data | Mike | 2018-08-13 00:00:00 |
| 3 | 1 | Data | Mike | 2018-08-20 00:00:00 |
| 4 | 1 | Data | Mike | 2018-08-15 00:00:00 |
+---------------+----------+-------+------------+---------------------+
Grouping: transition_id, field_id
I tried using a left join on the table itself, but this is only returning a single item.
SELECT t1.*
FROM table t1
LEFT JOIN table t2 ON (t1.event = t2.event AND t1.update < t2.update)
WHERE t2.update IS NULL AND t1.event = 81
Thank you for your help! André
Try this :
select x.* from tablename x
inner join
(select event, item, max(update) as mupdate from tablename
group by event, item)a
on x.event=a.event and x.item=a.item and x.update=a.mupdate