delete all recirds based the last 2 old dates

[sal21]

i need to delete all records where the last 2 date in filed DATA1 are old from the all dates...

example:

21/10/2013
21/10/2013
21/10/2013
12/11/2012
12/11/2012
12/11/2012
21/10/2013
21/10/2013
11/10/2013
11/10/2013
11/10/2013
04/10/2013
04/10/2013

in this my case, delete all records with:

04/10/2013
12/11/2012

[HansV]

Let's say the table is named MyTable. You can use the following SQL:

DELETE DATA1 FROM MyTable WHERE DATA1 IN (SELECT TOP 2 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1))

[sal21]

Let's say the table is named MyTable. You can use the following SQL:

DELETE DATA1 FROM MyTable WHERE DATA1 IN (SELECT TOP 2 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1))

tks!!!!!!!!!!!!!

But to be sure..., before to delete i need to see the result of query and make a test.
Instead to delete is possible to insert in field "test" a value "OK" on all records , for deletation.

[HansV]

To set the value of Test:

UPDATE MyTable SET Test = "OK" WHERE DATA1 IN (SELECT TOP 2 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1))

You can inspect the result by looking at

SELECT * FROM MyTable WHERE Test="OK"

The delete query can then be much simpler:

DELETE Test FROM MyTable WHERE Test="OK"

[sal21]

To set the value of Test:

UPDATE MyTable SET Test = "OK" WHERE DATA1 IN (SELECT TOP 2 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1))

You can inspect the result by looking at

SELECT * FROM MyTable WHERE Test="OK"

The delete query can then be much simpler:

DELETE Test FROM MyTable WHERE Test="OK"

Hans sorry me but not so simple...

in effect i would to maintain always in "live" the tHree newest recordset based date in DATA1.

example:

check first if the group by get > 3 dates.

if the dates in DATA1 > 3 delete all recodset oldest.

if the the group by of DATA1 have only 3 not action.

i hope you undestand me.

[HansV]

"but not so simple..." - you are asking a different question now! Originally, you wanted to delete only the records with the two earliest dates. Now, you want to delete all records except those with the three latest dates. That is not the same!

You can run the following three queries in order:

UPDATE MyTable SET Test = "OK"

UPDATE MyTable SET Test = "No" WHERE DATA1 IN (SELECT TOP 3 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1 DESC))

DELETE Test FROM MyTable WHERE Test = "OK"

[sal21]

"but not so simple..." - you are asking a different question now! Originally, you wanted to delete only the records with the two earliest dates. Now, you want to delete all records except those with the three latest dates. That is not the same!

You can run the following three queries in order:

UPDATE MyTable SET Test = "OK"

UPDATE MyTable SET Test = "No" WHERE DATA1 IN (SELECT TOP 3 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1 DESC))

DELETE Test FROM MyTable WHERE Test = "OK"

If i'm not wrong your code maintain oly one date in "live"....

[HansV]

So you didn't bother to try it?