show last 10 records

[Christo]

I have this script for showing news on a page, but i want it to only
show the last 10 records, as in the 10 records that were added to the
database last. the script shows the entries in descending order. Here
is a code snippet

Do While not rsNews.EOF
Response.Write("<table class=""tableborder"" border=""0""
cellspacing=""0"" cellpadding""0"" width=""100%"" ")
Response.Write("<tr>")
Response.Write("<td class=""tabletitle"" width=""100%"">")
Response.Write(rsNews("Title"))
Response.Write(" Posted on ")
Response.Write(rsNews("Date"))
Response.Write("</td></tr>")
Response.Write("<tr>")
Response.Write("<td class=""normal"" width=""100%"">")
Response.Write(rsNews("Body"))
Response.Write("</td></tr></table><br><br>")
rsNews.MoveNext
Loop

i tried adding a counter and making this

counter = 0
Do While not rsNews.EOF AND Counter <=10
'Response.Write code
counter = counter + 1
Loop

this didnt work either, Anyone know how i can do this? isnt there a way
to do it with an sql query?

any help much appreciated, thanks

Chris

 

[Anthony Jones]

I have this script for showing news on a page, but i want it to only
show the last 10 records, as in the 10 records that were added to the
database last. the script shows the entries in descending order. Here
is a code snippet

Do While not rsNews.EOF
Response.Write("<table class=""tableborder"" border=""0""
cellspacing=""0"" cellpadding""0"" width=""100%"" ")
Response.Write("<tr>")
Response.Write("<td class=""tabletitle"" width=""100%"">")
Response.Write(rsNews("Title"))
Response.Write(" Posted on ")
Response.Write(rsNews("Date"))
Response.Write("</td></tr>")
Response.Write("<tr>")
Response.Write("<td class=""normal"" width=""100%"">")
Response.Write(rsNews("Body"))
Response.Write("</td></tr></table><br><br>")
rsNews.MoveNext
Loop

i tried adding a counter and making this

counter = 0
Do While not rsNews.EOF AND Counter <=10
'Response.Write code
counter = counter + 1
Loop

this didnt work either, Anyone know how i can do this? isnt there a way
to do it with an sql query?

any help much appreciated, thanks

Chris

Modify the query used create the recordset:-

SELECT TOP 10 YourFields FROM YourTable ORDER BY afield
_that_you_can_sort_by DESC

However it does return them in reverse order. If that's seriously out of the
question use:-

SELECT * FROM (
SELECT TOP 10 YourFields FROM YourTable ORDER BY afield
_that_you_can_sort_by DESC)
ORDER BY afield _that_you_can_sort_by ASC

I'm not sure Access will be happy with that although SQL Server will (which
is why when asking a DB related question you should state the DB in use).

 

[Mike Brind]

Modify the query used create the recordset:-

SELECT TOP 10 YourFields FROM YourTable ORDER BY afield
_that_you_can_sort_by DESC

However it does return them in reverse order. If that's seriously out of
the
question use:-

SELECT * FROM (
SELECT TOP 10 YourFields FROM YourTable ORDER BY afield
_that_you_can_sort_by DESC)
ORDER BY afield _that_you_can_sort_by ASC

I'm not sure Access will be happy with that although SQL Server will
(which
is why when asking a DB related question you should state the DB in use).

Access is happy with that, as long as you include afield
_that_you_can_sort_by ASC in the SELECT TOP 10 clause. Although typically,
if you are pulling the most recent 10 stories from a database, you would
indeed want them in reverse order, so that the newest appears at the top of
the list.

 

[Christo]

Thank you I will test this out, unfortunately a new error popped up
in relation to the same set of scripts once that one is fixed ill get
this going thanks appreciated.

 

[Firas S Assaad]

Hi Christo,
Just in case that didnt work, try reversing your SQL statement
instead of ORDER BY [Field_Name] DESC make it ORDER BY [Field_Name] ASC
and then to view only ten records
for i=1 to 10
if rsNews.eof=false then
Response.write rsNews.fields("News")
end if
next


What i did is just listed the bottom records top
for example:
if the table in the descending order looks like this:
Record 1
Record 2
Record 3
Record 4

and you want to print the last two records
then order it by ascending
so it will look like this
Record 4
Record 3
Record 2
Record 1

and then run a loop that will print out the first two, which in fact
the last two but in different order.



Hope this helps



Best Regards
Firas S Assaad

 

[Mike Brind]

That's a very poor idea. What possible logic is there in replacing DESC
which gets just the records you want, to ASC and getting every record in the
table, only to discard all but 10 when you process them?

--
Mike Brind

Hi Christo,
Just in case that didnt work, try reversing your SQL statement
instead of ORDER BY [Field_Name] DESC make it ORDER BY [Field_Name] ASC
and then to view only ten records
for i=1 to 10
if rsNews.eof=false then
Response.write rsNews.fields("News")
end if
next


What i did is just listed the bottom records top
for example:
if the table in the descending order looks like this:
Record 1
Record 2
Record 3
Record 4

and you want to print the last two records
then order it by ascending
so it will look like this
Record 4
Record 3
Record 2
Record 1

and then run a loop that will print out the first two, which in fact
the last two but in different order.



Hope this helps



Best Regards
Firas S Assaad

Thank you I will test this out, unfortunately a new error popped up
in relation to the same set of scripts once that one is fixed ill get
this going thanks appreciated.

 

[Firas S Assaad]

Come on man,
Im just trying to help. This might be a poor idea, ButI m not a
professional here, so im learning too.

That's a very poor idea. What possible logic is there in replacing DESC
which gets just the records you want, to ASC and getting every record in the
table, only to discard all but 10 when you process them?

--
Mike Brind

Hi Christo,
Just in case that didnt work, try reversing your SQL statement
instead of ORDER BY [Field_Name] DESC make it ORDER BY [Field_Name] ASC
and then to view only ten records
for i=1 to 10
if rsNews.eof=false then
Response.write rsNews.fields("News")
end if
next


What i did is just listed the bottom records top
for example:
if the table in the descending order looks like this:
Record 1
Record 2
Record 3
Record 4

and you want to print the last two records
then order it by ascending
so it will look like this
Record 4
Record 3
Record 2
Record 1

and then run a loop that will print out the first two, which in fact
the last two but in different order.



Hope this helps



Best Regards
Firas S Assaad

Thank you I will test this out, unfortunately a new error popped up
in relation to the same set of scripts once that one is fixed ill get
this going thanks appreciated.

Mike Brind wrote:

I have this script for showing news on a page, but i want it to only
show the last 10 records, as in the 10 records that were added to
the
database last. the script shows the entries in descending order.
Here
is a code snippet

Do While not rsNews.EOF
Response.Write("<table class=""tableborder"" border=""0""
cellspacing=""0"" cellpadding""0"" width=""100%"" ")
Response.Write("<tr>")
Response.Write("<td class=""tabletitle"" width=""100%"">")
Response.Write(rsNews("Title"))
Response.Write(" Posted on ")
Response.Write(rsNews("Date"))
Response.Write("</td></tr>")
Response.Write("<tr>")
Response.Write("<td class=""normal"" width=""100%"">")
Response.Write(rsNews("Body"))
Response.Write("</td></tr></table><br><br>")
rsNews.MoveNext
Loop

i tried adding a counter and making this

counter = 0
Do While not rsNews.EOF AND Counter <=10
'Response.Write code
counter = counter + 1
Loop

this didnt work either, Anyone know how i can do this? isnt there a
way
to do it with an sql query?

any help much appreciated, thanks

Chris


Modify the query used create the recordset:-

SELECT TOP 10 YourFields FROM YourTable ORDER BY afield
_that_you_can_sort_by DESC

However it does return them in reverse order. If that's seriously out
of
the
question use:-

SELECT * FROM (
SELECT TOP 10 YourFields FROM YourTable ORDER BY afield
_that_you_can_sort_by DESC)
ORDER BY afield _that_you_can_sort_by ASC

I'm not sure Access will be happy with that although SQL Server will
(which
is why when asking a DB related question you should state the DB in
use).


Access is happy with that, as long as you include afield
_that_you_can_sort_by ASC in the SELECT TOP 10 clause. Although
typically,
if you are pulling the most recent 10 stories from a database, you
would
indeed want them in reverse order, so that the newest appears at the
top of
the list.