versioned-articles.sql

# Most recent version of all records and creation date of the oldest version
#
# "id","version_since","version_until","content"
# "1","2010-06-01","2010-06-05","Initial text."
# "1","2010-06-05","2010-06-10","Modified text."
# "1","2010-06-10","2037-12-31","Final text."
# "2","2010-05-01","2010-05-31","Second initial text."
# "2","2010-05-31","2010-06-10","Modified second text."
# "2","2010-06-10","2010-06-15","Modified again second text."
# "2","2010-06-15","2037-12-31","Second final text."

SELECT a.*, a_last.version_since AS creation_date FROM article a LEFT JOIN article a_next ON ( a.id = a_next.id AND a.version_since < a_next.version_since ) LEFT JOIN article a_last ON ( a.id = a_last.id AND a.version_since > a_last.version_since ) LEFT JOIN article a_before_last ON ( a_last.id = a_before_last.id AND a_last.version_since > a_before_last.version_since ) WHERE a_next.id IS NULL AND a_before_last.id IS NULL;