delete old records and keep 10 latest in sql compact

i'm using a sql compact database(sdf) in MS SQL 2008. in the table 'Job', each id has multiple jobs. there is a system regularly add jobs into the table.

I would like to keep the 10 latest records for each id order by their 'datec开发者_C百科ompleted' and delete the rest of the records

how can i construct my query? failed in using #temp table and cursor

Well it is fast approaching Christmas, so here is my gift to you, an example script that demonstrates what I believe it is that you are trying to achieve. No I don't have a big white fluffy beard ;-)

CREATE TABLE TestJobSetTable
(
    ID INT IDENTITY(1,1) not null PRIMARY KEY,
    JobID INT not null,
    DateCompleted DATETIME not null
);

--Create some test data
DECLARE @iX INT;
SET @iX = 0
WHILE(@iX < 15)
BEGIN
    INSERT INTO TestJobSetTable(JobID,DateCompleted) VALUES(1,getDate())
    INSERT INTO TestJobSetTable(JobID,DateCompleted) VALUES(34,getDate())
    SET @iX = @iX + 1;
        WAITFOR DELAY '00:00:0:01'
END
--Create some more test data, for when there may be job groups with less than 10 records.
SET @iX = 0
WHILE(@iX < 6)
BEGIN
    INSERT INTO TestJobSetTable(JobID,DateCompleted) VALUES(23,getDate())
    SET @iX = @iX + 1;
        WAITFOR DELAY '00:00:0:01'
END

--Review the data set
SELECT * FROM TestJobSetTable;


--Apply the deletion to the remainder of the data set.
WITH TenMostRecentCompletedJobs AS
(

    SELECT ID, JobID, DateCompleted
    FROM TestJobSetTable A
    WHERE ID in
    (   
        SELECT TOP 10 ID 
        FROM TestJobSetTable
        WHERE JobID = A.JobID
        ORDER BY DateCompleted DESC
    )
)
--SELECT * FROM TenMostRecentCompletedJobs ORDER BY JobID,DateCompleted desc;
DELETE FROM TestJobSetTable 
WHERE ID NOT IN(SELECT ID FROM TenMostRecentCompletedJobs)

--Now only data of interest remains
SELECT * FROM TestJobSetTable

DROP TABLE TestJobSetTable;

How about something like:

DELETE FROM
    Job
WHERE NOT
    id IN (
       SELECT TOP 10 id
       FROM Job
       ORDER BY datecompleted)

This is assuming you're using 3.5 because nested SELECT is only available in this version or higher.

I did not read the question correctly. I suspect something more along the lines of a CTE will solve the problem, using similar logic. You want to build a query that identifies the records you want to keep, as your starting point.

Using CTE on SQL Server Compact 3.5