🚀 go-pugleaf

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