Post reply

How to limit number of rows

  • March 1, 2005 at 6:04 am

    #65668

    How can i say fetch records from 20000 to 20025 using query or thru ADO.

    My Table is: Create table MyLog (name nvarchar(250),EntTime datetime,Ticks nvarchar(10),Data ntext)

    The problem is i want to display records page by page with a page size of 25. My table does not have any primary keys. The number of records has grown to 5 lakhs. I have to fetch it like" select * from MyLog order by EntTime,Ticks - LIMIT 20000,25".For first few pages im getting records immediately. but then it takes much time for the next sets.right now im using ADO.

     

     

     

  • March 1, 2005 at 6:08 am

    #544580

    Why is there no Primary Key on that table? Almost all solutions you will find when you search the fora here or google for SQL Server and paging will rely on the existance of a Primary Key.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 1, 2005 at 6:35 am

    #544585

    If i have a table like, User[name(primary key),age,Data], and im paging it. if there is no sorting, how will it be possible to fetch next set of records. when we sort if the number of records is bery high, the sorting takes too much time. How will i do for the qry to fetch records from 100 to 125.i can use top to limit number of records but how to fetch from  the start index 100.

    select top 25 from users  --start at 100th

     

  • March 1, 2005 at 6:43 am

    #544589

    Okay, okay, I'll help you a bit with searching

    http://www.microsoft.com/sql/techinfo/tips/administration/resultset.asp

    http://www.aspfaqs.com/webtech/062899-1.shtml

    http://www.google.de/search?hl=de&q=sql+server+paging&meta=

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 1, 2005 at 7:27 am

    #544595

    Thanks, but most of them need primary keys and sorting.

  • March 1, 2005 at 7:53 am

    #544613

    That is the point Frank is trying to make - you need a primary key and sorting as well.

    /Kenneth

  • March 2, 2005 at 6:57 am

    #544856

    How unique is the Enttime field? If it is a true datetime (down to ms) then they should be unique in which case you could use that as your primary key. If not why not add a simple identity column?

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply