Delete SQL looping

Question

I want to keep only 1000 entries for each clientid. The code below does what I want but does not loop through the clientid, instead keeping 1000 total of any of the clients.

Is there a way to do this in sql? I was told i need a cursor, but I am hoping not. SQL 2005

DECLARE @ids TABLE ( id int )
DECLARE @clients TABLE ( clientid varchar(20) )

INSERT INTO @clients (clientid)
SELECT select distinct clientid FROM tRealtyTrac 

INSERT INTO @ids (id)
SELECT top 1000 id FROM tRealtyTrac WHERE clientid in (select clientid from @clients)

DELETE trealtytrac WHERE id NOT IN (select id from @ids)

Answer 1

in Oracle:

DELETE from CLIENTS
where CLIENT_ID = 'xxx' and
rownum > 1000

Answer 2

Is this SQL Server 2005 or later? What about something like

INSERT INTO @ids (id)
SELECT id FROM (
    SELECT id, RANK() OVER (PARTITION BY clientid ORDER BY id) AS Rank FROM tRealtyTrac
) t
WHERE t.Rank <= 1000

Answer 3

I think the "in Oracle" answer will delete the newest entries. Be careful with that one!