Alejandro Garcia schreef:

>
>
> Hi! I have a patient's table with 3 fields:
> NUMPAT is the number of the patient
> TREATMENT is a treatment code
> DATE a date
> There are many dates for each patient and I need to select the minimun
> date for each patient. This SQL works fine:
>
> SELECT T.NUMPAT,T.TREATMENT,T.DATE
> FROM MYTABLE T
> WHERE
> T.DATE=
> (SELECT MIN(Q.DATE) FROM
> MYTABLE Q WHERE T.NUMPAT = Q.NUMPAT )
> order by T.NUMPAT
>
> By now I need to delete that minimun date for each patient, and simply
> change the first SELECT for a DELETE is not working... how could I?
> Sorry for this so simple question...
>
> ---------------------------------
>
> Enviado desde Correo Yahoo!
> Disfruta de una bandeja de entrada más inteligente..
>
>

Alejandro,

Apart from the fact that I think what you want is a bit strange- imagine
a patient that came in for the first time yesterday, and was given a
first treatment, the record describing this would be deleted today!
But apart from that, I am afraid the only thing to do what you want is
to create a stored procedure, like this:

declare MINDATE date;
declare NUMPAT integer;
begin
for SELECT MIN("DATE") as MINDATE
FROM MYTABLE
group by NUMPAT
into :MINDATE, :NUMPAT do
delete from MYTABLE where "DATE" = :MINDATE and NUMPAT = :NUMPAT;
end

I hope this helps,

Hans