show last 10 records

Discussion in 'ASP General' started by Christo, Nov 8, 2006.

  1. Christo

    Christo Guest

    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
     
    Christo, Nov 8, 2006
    #1
    1. Advertising

  2. "Christo" <> wrote in message
    news:...
    > 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).
     
    Anthony Jones, Nov 8, 2006
    #2
    1. Advertising

  3. Christo

    Mike Brind Guest

    "Anthony Jones" <> wrote in message
    news:...
    >
    > "Christo" <> wrote in message
    > news:...
    >> 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.

    --
    Mike Brind
     
    Mike Brind, Nov 8, 2006
    #3
  4. Christo

    Christo Guest

    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:
    > "Anthony Jones" <> wrote in message
    > news:...
    > >
    > > "Christo" <> wrote in message
    > > news:...
    > >> 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.
    >
    > --
    > Mike Brind
     
    Christo, Nov 13, 2006
    #4
  5. 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
    Christo wrote:
    > 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:
    > > "Anthony Jones" <> wrote in message
    > > news:...
    > > >
    > > > "Christo" <> wrote in message
    > > > news:...
    > > >> 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.
    > >
    > > --
    > > Mike Brind
     
    Firas S Assaad, Nov 14, 2006
    #5
  6. Christo

    Mike Brind Guest

    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


    "Firas S Assaad" <> wrote in message
    news:...
    > 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
    > Christo wrote:
    >> 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:
    >> > "Anthony Jones" <> wrote in message
    >> > news:...
    >> > >
    >> > > "Christo" <> wrote in message
    >> > > news:...
    >> > >> 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.
    >> >
    >> > --
    >> > Mike Brind

    >
     
    Mike Brind, Nov 14, 2006
    #6
  7. Come on man,
    Im just trying to help. This might be a poor idea, ButI m not a
    professional here, so im learning too.


    Mike Brind wrote:
    > 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
    >
    >
    > "Firas S Assaad" <> wrote in message
    > news:...
    > > 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
    > > Christo wrote:
    > >> 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:
    > >> > "Anthony Jones" <> wrote in message
    > >> > news:...
    > >> > >
    > >> > > "Christo" <> wrote in message
    > >> > > news:...
    > >> > >> 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.
    > >> >
    > >> > --
    > >> > Mike Brind

    > >
     
    Firas S Assaad, Nov 15, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luke Airig
    Replies:
    0
    Views:
    822
    Luke Airig
    Dec 31, 2003
  2. Luis P. Mendes
    Replies:
    2
    Views:
    315
    Luis P. Mendes
    Nov 8, 2006
  3. Dan

    Delete records or update records

    Dan, May 10, 2004, in forum: ASP General
    Replies:
    1
    Views:
    487
    Ray at
    May 10, 2004
  4. Replies:
    3
    Views:
    706
    Anthony Jones
    Nov 2, 2006
  5. ll
    Replies:
    2
    Views:
    685
    Thomas 'PointedEars' Lahn
    May 1, 2008
Loading...

Share This Page