query needed

[Yang Li Ke]

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
--

[Erland Sommarskog]

Re: query needed

[posted and mailed, please reply in news]

Yang Li Ke (yanglike@sympa tico.ca) writes:[color=blue]
> 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 :)[/color]

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/techinfo/productdoc/2000/books.asp

[Yannick Turgeon]

Re: query needed

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@sympa tico.ca> wrote in message
news:TgJFb.4375 $d%1.948103@new s20.bellglobal. com...[color=blue]
> 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
> --
>
>
>
>[/color]