query needed

Hi guys!

I need a query to remove all records from my database except the
latest 100 added. I use an id as the primary key. But those records
are also often removed manually depending on some other values.

So I can not simply use : delete * from db where id < last_id - 100
because the last id could be 348 while the 100th would be 124.

I hope somone understands what I mean and can help :)

Thank you

Yang
--[posted and mailed, please reply in news]

Yang Li Ke (yanglike@.sympatico.ca) writes:
> I need a query to remove all records from my database except the
> latest 100 added. I use an id as the primary key. But those records
> are also often removed manually depending on some other values.
> So I can not simply use : delete * from db where id < last_id - 100
> because the last id could be 348 while the 100th would be 124.
> I hope somone understands what I mean and can help :)

If you want people to understand what you mean, you need to provide:

o CREATE TABLE statment of the table.
o INSERT statements with sample data (and possibly also DELETE
statements in this case).
o The desired output.

With this information you can get a tested solution.

Without this information, you can get a untested guess. Here my stab:

DELETE tbl
WHERE id < (SELECT MIN(id)
FROM (SELECT TOP 100 id
FROM tbl
ORDER BY id DESC) x)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Yang,

I would try something like that. No need to say to test it before really
using it because I did not try it!

DELETE FROM db
WHERE id NOT IN (
SELECT TOP 100 id
FROM db
ORDER BY id DESC)

"Yang Li Ke" <yanglike@.sympatico.ca> wrote in message
news:TgJFb.4375$d%1.948103@.news20.bellglobal.com.. .
> Hi guys!
> I need a query to remove all records from my database except the
> latest 100 added. I use an id as the primary key. But those records
> are also often removed manually depending on some other values.
> So I can not simply use : delete * from db where id < last_id - 100
> because the last id could be 348 while the 100th would be 124.
> I hope somone understands what I mean and can help :)
> Thank you
> Yang
> --
>