1

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é

Fiddle: http://sqlfiddle.com/#!9/b24ddb/4

AUe
  • 35
  • 4
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Aug 20 '18 at 09:16
  • Thank you Strawberry, helpful hint! – AUe Aug 20 '18 at 09:32
  • BTW, I don't understand why you would expect more than one row to be returned! – Strawberry Aug 20 '18 at 09:34
  • My apologies, you are right. I have corrected the data. The table represents a log file - every time VALUE is updated it created a new entry for the same TRANSITION_ID & FIELD_ID. I want to fetch the latest status per TRANSITION_ID and FIELD_ID. – AUe Aug 20 '18 at 09:46
  • Well, you have a solution now, courtesy of fa06 – Strawberry Aug 20 '18 at 09:49

1 Answers1

0

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
Fahmi
  • 37,315
  • 5
  • 22
  • 31