RE: Deleting with SQL

I have a file that is a log of who ran programs on what date that I need to
purge but the purge has dual criteria. I need to purge based on keeping x
number of entries and entries before x date. For example, I need to keep
all entries after 02/15/2015 AND also the last 30 entries. So, if an entry
falls inside of either of these criteria, I keep it. The user is immaterial
to the result set.



I'm not sure I have explained that very well but in the event that I have,
can anyone tell me how to do it through SQL?

John,

Basically what Chuck said.

It is possible but you have to do it in a two step process using a fairly complex SQL statement to first get the records selected, then another SQL can use that data to delete intended records.

The top CTE will generate a unique ranking for each record in the file, then the fullselect referencing the new data selects data based on the criteria defined. (Top 30 records or date is greater than date.)

With All_Data_Selected As (SelectCustomer#,Order#,OrderDate,row_number() over(Order by OrderDate desc) As date_orderFromOrders)SelectCustomer#,Order#,OrderDateFromAll_Data_SelectedWhereOrderDate >= Current Date - 1 MonthsOrdate_order < 30

Jim