I frequently solve a completely different problem in a way that may be
useful for you - I 'flatten out' tables so that the results can be used
by statistical programs that don't understand the word 'relational'

I start doing this by introducing a sequence number, setting this
number to 1 for all rows initially, and then update it using queries
like

UPDATE PIVTable p1
SET p1.SequenceNo = 2
WHERE p1.SequenceNo = 1
AND EXISTS(SELECT * FROM PIVTable p2
WHERE p1.PersonId = p2.PersonId
AND p1.PIVDate > P2.PIVDate
AND p2.SequenceNo = 1)

then

UPDATE PIVTable p1
SET p1.SequenceNo = 3
WHERE p1.SequenceNo = 2
AND EXISTS(SELECT * FROM PIVTable p2
WHERE p1.PersonId = p2.PersonId
AND p1.PIVDate > P2.PIVDate
AND p2.SequenceNo = 2)

and so on (actually, things are slightly more complicated for me, since
I also have to deal with the situation of things happening
simultaneously).

This sequence number may or may not be appropriate to set in the
PIVTable itself (it isn't ideal for concurrency or if the number
changes from request to request, but concurrency may not be an issue
with systems like yours).

Once you have this sequence number in your PIVTable (or in a copy that
you make), you can simply do

SELECT * FROM PIVTable WHERE SequenceNo = 1
SELECT * FROM PIVTable WHERE SequenceNo = 2
SELECT * FROM PIVTable WHERE SequenceNo = 3

etc. to get the datasets that you seem to require (in my situation, I
use lots of LEFT JOINs, but that doesn't seem to be useful for you).

HTH,
Set

--- In firebird-support@yahoogroups.com, Alejandro Garcia wrote:
>
> THANKS SO MUCH Kjell, Sean and Helen.
>
> Regarding the reasons for deleting patients records I'm only
> deleting temporal files and it's the way I found to solve the
> following problem:
> Some patients come to hospital after discharge once a year for a
> specific evaluation called PIV. So I have a table with all patients
> PIVs. I was asked to create a table with all patients first PIV,
> another table with all patient's second PIV, and so on. To find the
> first PIV I need the minimun date, to find the second PIV I delete
> that minimun and look for the minimun in the remaining table and so
> on... always working in temporal tables.