SQL logic for version history including table

Hi,

I've got a table which includes complete versioning and history for each
entry. The fields in question are:

- createdate (date of creation of this row)
- vno (version number, increased by 1 for each new version)
- active ('Y' for newest version, 'N' for all older ones, simply set to
'N' for all versions, if a row is deleted)
- dam_id (key value, but not unique, same value for each
version of an entry)

I want to select the following data:

(1) All the changes, which were made since a specific date in the past,
ordered by their date of creation descending and a name field in ascending
way.

(2) As I want to set a special mark in the output if that was a new entry
or just the change of an old existing entry, I need to check the version
number for each date of creation.

(3) As there can be multiple versions on one day, I need to check for the
lowest version number on each date of creation to see, wether it really
was a change or a new entry.

(4) Finally I need to exclude all the entries, if they don't have a valid
entry on the current day any more, which means they were deleted after the
date the last entry was made -> short: only entrys which have a active='Y'
entry are allowed to be selected.

Thought quite a while about it and came to the following conclusion:

SELECT d.field1,
d.field2, ...
d.createdate,
(SELECT d4.createdate
FROM dam d4
WHERE d.dam_id = d4.dam_id
ORDER BY createdate ASC LIMIT 1) AS firstdate
FROM dam d
WHERE createdate >= '1999-06-01'
AND vno=(SELECT d2.vno
FROM dam d2
WHERE d2.dam_id=d.dam_id
AND d2.createdate=d.createdate
ORDER BY vno ASC LIMIT 1)
AND EXISTS
(SELECT *
FROM dam d3
WHERE d3.dam_id=d.dam_id
AND d3.active=d.active
AND d.active='Y')
ORDER BY
createdate DESC,
dam ASC
LIMIT 200;

The explain plan looks like:

NOTICE: QUERY PLAN:

Limit (cost=137625.77..137625.77 rows=200 width=73)
-> Sort (cost=137625.77..137625.77 rows=395 width=73)
-> Seq Scan on dam d (cost=0.00..137608.74 rows=395 width=73)
SubPlan
-> Limit (cost=7.24..7.24 rows=1 width=4)
-> Sort (cost=7.24..7.24 rows=1 width=4)
-> Index Scan using ix_dam_dam_id on dam d4
(cost=0.00..7.23 rows=1 width=4)
-> Limit (cost=7.24..7.24 rows=1 width=4)
-> Sort (cost=7.24..7.24 rows=1 width=4)
-> Index Scan using ix_dam_dam_id on dam d2
(cost=0.00..7.23 rows=1 width=4)
-> Result (cost=0.00..7.23 rows=1 width=287)
-> Index Scan using ix_dam_dam_id on dam d3
(cost=0.00..7.23 rows=1 width=287)

Maybe someone wants to discuss about that approach with me or wants to
point out possible errors. All opinions are welcome.

Kind regards
... Ralph ...