Delete min does not work

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 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

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..

"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) 

"Wayne Niddery \(TeamB\)" <wniddery@chaffaci.on.ca> wrote:
>"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) 
>

THANKS SO MUCH WAYNE!!
MUCHAS GRACIAS!!!!!!