How to delete last N records in SQL Server 2008 R2 database? [duplicate]

Question

I have a SQL Server 2008 R2 Express database with one table with rows. And when database size became about 10gb, I need to clean the N last records from this table.

I need something like this, but for SQL Server

DELETE FROM mytable 
WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID ASC LIMIT 100)

Thanks.

Database structure:

strSQL = "SELECT DateAndTime
                ,TagName
                ,Val
                ,SetPoint
                ,Limit_H
                ,Limit_L
                ,Result 
          FROM dbo.Statistic...."

UPD. It is not depend on "DateAndTime", becouse i have to free space, but 1 day (or 1 week) may consist only 1 record, as i understand...so i need delete 200K records.

Answer 1

Here you go:

DELETE FROM mytable 
WHERE %%physloc%% IN (SELECT TOP 100 %%physloc%% AS RN FROM mytable ORDER BY RN DESC);