Delete records by latest date

Hello,
Can anyone tell me how to delete records from a table
based on dates. For example I have a table of property
sales data. Each property could sell multiple times over
several years. So, the ID # of the property would be the
same each time it sold, but the sale date would be
different. I only want to keep one sale record for each
property ID # and I want it to be the latest sale date.
Here is a sample:
PIN SALEDATE TYPE
6842-564-299 2/12/01 A
6842-564-299 4/17/03 A
6724-312-765 9/5/02 B
6993-411-772 7/21/03 A
The first 2 records above have a duplicate PIN. So I want
to delete the one with the earliest date and keep the
other. Can anyone tell me how to do this in SQL Server
2000. Any help would be greatly appreciatedSo many choices :)
Assuming your table is called YourTable: Also assuming the sales date is a
string (if not you can remove the convert function.
delete YourTable
from YourTable
join
(select PIN, max(SALESDATE) as SALESDATE from YourTable group by PIN having
count(*) > 1) foo
on YourTable.PIN = foo.PIN and convert(smalldatetime,YourTable.SALESDATE) <
convert(smalldatetime,foo.SALESDATE)
Mark
"Shane Sprouse" <shane@.co.greenwood.sc.us> wrote in message
news:0b5301c3aa2f$ddad68e0$a001280a@.phx.gbl...
> Hello,
> Can anyone tell me how to delete records from a table
> based on dates. For example I have a table of property
> sales data. Each property could sell multiple times over
> several years. So, the ID # of the property would be the
> same each time it sold, but the sale date would be
> different. I only want to keep one sale record for each
> property ID # and I want it to be the latest sale date.
> Here is a sample:
> PIN SALEDATE TYPE
> 6842-564-299 2/12/01 A
> 6842-564-299 4/17/03 A
> 6724-312-765 9/5/02 B
> 6993-411-772 7/21/03 A
>
> The first 2 records above have a duplicate PIN. So I want
> to delete the one with the earliest date and keep the
> other. Can anyone tell me how to do this in SQL Server
> 2000. Any help would be greatly appreciated
>

DELETE Problem

Hi Guys,

I have this strange problem. Take the following SQL i wrote which
erturns a list of dates lower than the one entered in a table :-

SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE ACTVTY_DT IN

(SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW

WHERE SUBSTR(ACTVTY_DT,5,2) < '02'
)

UNION

SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE ACTVTY_DT IN

(SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW

WHERE SUBSTR(ACTVTY_DT,1,2) < '03'
AND
SUBSTR(ACTVTY_DT,5,2) = '02'
)

UNION

SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE ACTVTY_DT IN

(SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW

WHERE SUBSTR(ACTVTY_DT,3,2) < '27'
AND
SUBSTR(ACTVTY_DT,1,2) = '03'
AND
SUBSTR(ACTVTY_DT,5,2) = '02'
);

I had to do this in this format as whoever created the table made the ACTVTY_DT a CHAR(06) instead of a DATE !
This works fine and gives me back my required results.

The problem is however - How do I get SQL to delete these results now
from the HACPRP.EQ_POOL_ACT_VIEW table ?

No matter what i try it just falls over and will not delete the records.
Is it possible to do ?

I really need help on this one, so any comments would be really appreciated.

Peter.just replace the select * with the word DELETE, most likely cannot do the UNION DELETE so it most likely will have to be three seperate statements without the union.

let me know if that does not work|||Try this:

DELETE FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE ACTVTY_DT IN
(SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE SUBSTR(ACTVTY_DT,5,2) < '02'
OR (SUBSTR(ACTVTY_DT,1,2) < '03'
AND SUBSTR(ACTVTY_DT,5,2) = '02')
OR (SUBSTR(ACTVTY_DT,3,2) < '27'
AND SUBSTR(ACTVTY_DT,1,2) = '03'
AND SUBSTR(ACTVTY_DT,5,2) = '02'));

also:

DELETE FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE TO_DATE(ACTVTY_DT,'MMDDYY') < '27-MAR-2003';

;)|||hi,

or just type delete from ( <your query)

hope this helps