🚀 go-pugleaf

"Alejandro" <aleplgr@yahoo.es> wrote in message 
news:47e127be$1@newsgroups.borland.com...
>
> There are many dates for each patient and for selecting the minimun date 
> for each patient this SQL works fine:
>
> SELECT T.NUMPAT,T.TREATMEN T,T.DATE
> FROM MYTABLE T
> WHERE
> T.DATE=
> (SELECT MIN(Q.DATE) FROM
> MYTABLE Q WHERE T.NUMPAT = Q.NUMPAT )
> order by T.NUMPAT

If all you want is minimum date for each patient, regardless of treatment, 
then this should be more efficient by using Group By:

 SELECT T.NUMPAT, MIN(T.DATE)
 FROM MYTABLE T
 GROUP BY T.NUMPAT

> But for deleting the minimum date for each patient I'm trying this:
> DELETE
> FROM MYTABLE T
> WHERE
> T.DATE=
> (SELECT MIN(Q.DATE) FROM
> MYTABLE Q WHERE T.NUMPAT = Q.NUMPAT )
>
> But deletes the whole table, I don't know why..

Because the only criteria you've set is that a record match a selected date 
without regard to which patient, and as it deletes, it will match each next 
record to the now existing minimum date.

InterBase cannot compare two fields using a single subquery and so there's 
really no way to formulate a single delete statement to do this. The only 
way I can see to do it is via a stored procedure. The following procedure 
will work:

CREATE PROCEDURE "DELETEOLDESTTREAMENT"
AS
declare variable numpat integer;
declare variable tdate date;
begin
  for select numpat, min(tdate)
    from patients
    group by numpat
  into numpat, tdate do
  begin
    delete from patients where numpat = :numpat and tdate = :tdate;
  end
end

Run this each time you want the oldest treatment for each patient deleted:
  execute procedure deleteoldesttreatment

-- 
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca)