How to delete all but "n" most recent records  
Author Message
TobyMynott





PostPosted: Wed Dec 24 08:37:44 CST 2003 Top

SQL Server Developer >> How to delete all but "n" most recent records

Sorry for the mundane nature -> presumption of this request, but it's crunch
time and my Beginner's SQL manual is still at home unwrapped under the tree:

INSERT trigger needs to delete all but the most recent "n" records (say
n=100) each time it runs from the trigger(ing) table, which has DateTime
column/field. Does anyone have a good way to suggest doing this?


Thanks for your help!

Buon Natale!
Frohliche Weinachten!
Felice Navidad!
Merry Christmas!
Happy Holidays!

SQL Server156  
 
 
Tom





PostPosted: Wed Dec 24 08:37:44 CST 2003 Top

SQL Server Developer >> How to delete all but "n" most recent records This is a multi-part message in MIME format.

------=_NextPart_000_008C_01C3CA01.95265040
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit

If you could post your DDL, we could come up with a better solution.
However, this may do it:

create trigger tri_MyTable on MyTable after insert
as

return

delete MyTable
where
MyCol not in (select top 100 MyCol
from MyTable
order by MyCol desc)
go


--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql




Sorry for the mundane nature -> presumption of this request, but it's crunch
time and my Beginner's SQL manual is still at home unwrapped under the tree:

INSERT trigger needs to delete all but the most recent "n" records (say
n=100) each time it runs from the trigger(ing) table, which has DateTime
column/field. Does anyone have a good way to suggest doing this?


Thanks for your help!

Buon Natale!
Frohliche Weinachten!
Felice Navidad!
Merry Christmas!
Happy Holidays!


------=_NextPart_000_008C_01C3CA01.95265040
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#d4d0c8>
<DIV><FONT face=3DTahoma size=3D2>If you could post your DDL, we could =
come up with=20
a better solution.&nbsp; However, this may do it:</FONT></DIV>
<DIV><FONT face=3DTahoma size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>create trigger tri_MyTable on =
MyTable after=20
insert</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>as</FONT></DIV>

0</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp; =
return</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>delete MyTable</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>where</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp; MyCol not in =
(select top=20
100 MyCol</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp; from=20
MyTable</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>&nbsp;&nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp; order=20
by MyCol desc)</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>go</FONT></DIV>
<DIV><FONT face=3DTahoma size=3D2></FONT>&nbsp;</DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV>&nbsp;</DIV>
<DIV>---------------------------------------------------------------<BR>T=
homas=20
A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =
Server=20
Professional<BR>Toronto, ON Canada<BR><A=20
href=3D"http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>"Tom Roach" &lt;<A=20

wrote in=20
message <A=20

@TK2MSFTNGP10.phx.gbl</A>...</DIV>Sorry=20
for the mundane nature -&gt; presumption of this request, but it's=20
crunch<BR>time and my Beginner's SQL manual is still at home unwrapped =
under the=20
tree:<BR><BR>INSERT trigger needs to delete all but the most recent "n" =
records=20
(say<BR>n=3D100) each time it runs from the trigger(ing) table, which =
has=20
DateTime<BR>column/field.&nbsp; Does anyone have a good way to suggest =
doing=20
this?<BR><BR><BR>Thanks for your help!<BR><BR>Buon Natale!<BR>Frohliche=20
Weinachten!<BR>Felice Navidad!<BR>Merry Christmas!<BR>Happy=20
Holidays!<BR><BR></BODY></HTML>

------=_NextPart_000_008C_01C3CA01.95265040--

 
 
Dan





PostPosted: Wed Dec 24 09:24:59 CST 2003 Top

SQL Server Developer >> How to delete all but "n" most recent records Try:

DELETE FROM MyTable
WHERE MyDate <=
(
SELECT MAX(MaxDate)
FROM
(
SELECT MAX(a.MyDate) AS MaxDate
FROM MyTable a
JOIN MyTable b ON
b.MyDate > a.MyDate
GROUP BY a.MyDate
HAVING COUNT(*) >= 100
) AS MaxDate
)

--
Hope this helps.

Dan Guzman
SQL Server MVP




> Sorry for the mundane nature -> presumption of this request, but it's
crunch
> time and my Beginner's SQL manual is still at home unwrapped under the
tree:
>
> INSERT trigger needs to delete all but the most recent "n" records (say
> n=100) each time it runs from the trigger(ing) table, which has DateTime
> column/field. Does anyone have a good way to suggest doing this?
>
>
> Thanks for your help!
>
> Buon Natale!
> Frohliche Weinachten!
> Felice Navidad!
> Merry Christmas!
> Happy Holidays!
>
>