Get events history from a table that holds events and their old versions at the same time

1k views Asked by At

I have a table with events "events" that holds all events and their older versions. I have these columns:

  • "id" primary,
  • "origin_id" here I keep the ID of the original event,
  • "date_added" DATETIME column.

When I first add an event its "origin_id" get its "id" value. When I change this event I create a new event with the same "origin_id" as the first one, new auto-incremented "id" and new "date_added" of course.

How to get a list with all current events from table, without their old versions ordered by the starting date "start" - a DATETIME column again?

So if I have a 3 events and each of them has several revisions/updates I want to get only the last update for each of them.

2

There are 2 answers

0
Yasen Zhelev On BEST ANSWER

Amm I think I got it on my own :)

SELECT *
FROM events WHERE id  
IN (
SELECT MAX( id )
FROM events
GROUP BY origin_id
)  
ORDER BY start ASC
1
Andriy M On

What about this?

SELECT MAX(id), origin_id, MAX(date_added), start
FROM events
GROUP BY origin_id, start
ORDER BY start ASC

It is certainly not the same as your own solution, so I think you are free to accept yours if mine doesn't work for you and if nothing better turns up.