Kjell Rilbe wrote:

> Alejandro Garcia wrote:
> > 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...
>
> I would have expected this to work:
> DELETE
> FROM MYTABLE T
> WHERE
> T.DATE=
> (SELECT MIN(Q.DATE) FROM
> MYTABLE Q WHERE T.NUMPAT = Q.NUMPAT )

Correcting myself:

DELETE
FROM MYTABLE T
WHERE cast(T.NUMPAT as varchar(200)) || ':' || cast(T.DATE as
varchar(200)) =
(SELECT cast(T.NUMPAT as varchar(200)) || ':' || cast(MIN(Q.DATE) as
varchar(200)) FROM
MYTABLE Q WHERE T.NUMPAT = Q.NUMPAT )

Inefficient, yes, but should work unless casting a date field loses
precision to the extent that you get duplicates. Does it?

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64