DELECT FROM table
WHERE (ProdID,ChangeDate) not in
(SELECT ProdID,MAX(ChangeDate) FROM table
GROUP BY ProdID)
I hope this works.
On 12/7/05, Alex <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have a table where I store changes made to an order. The looks like
> ProdID, ChangeDate, Change1, Change2, ... etc.
> Some ProdIDs have multiple records.
>
> Is there an easy way to delete all records of a ProdID except the most
> recent (ChangeDate is timestamp) one? Preferably in one SQL statement?
>
> Thanks for any advise
>
> Alex
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
Regards
Pandu
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
