|
|
How to delete all but "n" most recent records |
|
Author |
Message |
TobyMynott
|
Posted: 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
|
Posted: 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. However, this may do it:</FONT></DIV>
<DIV><FONT face=3DTahoma size=3D2></FONT> </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> =
return</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </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> MyCol not in =
(select top=20
100 MyCol</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2> =
from=20
MyTable</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2> =
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> </DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </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> </DIV>
<DIV> </DIV>
<DIV>"Tom Roach" <<A=20
wrote in=20
message <A=20
@TK2MSFTNGP10.phx.gbl</A>...</DIV>Sorry=20
for the mundane nature -> 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. 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
|
Posted: 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!
>
>
|
|
|
|
|
|
|