Hi 

How do I display the 10 latest records and delete the rest?

Any help will be appreciated

Thanks
Ryan

You may need a field to identify the new records. If a record is inserted 
with a datetime field that records the time during insertion. You can 
select top 10 records, order by datetime desc and then delete the rest 
that not in the 10 records. Alternatively, if the records have a 
sequencial id fileds, it can be done the same way using order by id desc. 
Hope this would help.

Tom


> Hi 

> How do I display the 10 latest records and delete the rest?

> Any help will be appreciated

> Thanks
R> yan

Hi Tom

Thanks for your reply

I understand the concepts, I'm new to asp so how do I do this in 
programming terms?


> You may need a field to identify the new records. If a record is 
inserted 
w> ith a datetime field that records the time during insertion. You can 
s> elect top 10 records, order by datetime desc and then delete the rest 
t> hat not in the 10 records. Alternatively, if the records have a 
s> equencial id fileds, it can be done the same way using order by id 
desc. 
H> ope this would help.

> Tom

> 
>>  Hi 

> > How do I display the 10 latest records and delete the rest?

> > Any help will be appreciated

> > Thanks
R> > yan

Hi, the following code would do this. Thanks.

------asp code-------
set rs=server.CreateObject("ADODB.RecordSet")
rs.Open "GetTenRecordsDeleteRest", _
           MyConnectionString, _
           adOpenForwardOnly, adLockReadOnly, _
           adCmdStoredProc
do while not rs.EOF
	Response.Write rs.Fields(0) & "<br>" 'display any fields you like
	rs.movenext
loop
rs.Close
set rs=nothing
-----------------------

Assume using a sql database.
table name: MyTable that has an integer id field as primary key and other 
fields
Stored procedure to get 10 records would be like:

--------sql databae code-----------
CREATE PROCEDURE GetTenRecordsDeleteRest AS
select top 10 * from MyTable order by id desc
delete from MyTable 
where id not in (select top 10 id from MyTable order by id desc)
------------------------------------

thanks Tom

-----Original Message-----
From: Tom Zhang [mailto:tzhang@p...]
Sent: 29 March 2002 05:24
To: ASP Web HowTo
Subject: [asp_web_howto] Re: Displayning 10 records only


Hi, the following code would do this. Thanks.

------asp code-------
set rs=server.CreateObject("ADODB.RecordSet")
rs.Open "GetTenRecordsDeleteRest", _
           MyConnectionString, _
           adOpenForwardOnly, adLockReadOnly, _
           adCmdStoredProc
do while not rs.EOF
	Response.Write rs.Fields(0) & "<br>" 'display any fields you like
	rs.movenext
loop
rs.Close
set rs=nothing
-----------------------

Assume using a sql database.
table name: MyTable that has an integer id field as primary key and other
fields
Stored procedure to get 10 records would be like:

--------sql databae code-----------
CREATE PROCEDURE GetTenRecordsDeleteRest AS
select top 10 * from MyTable order by id desc
delete from MyTable
where id not in (select top 10 id from MyTable order by id desc)
------------------------------------

---

Improve your web design skills with these new books from Glasshaus.

Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20