Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2003
    Posts
    18

    Unanswered: How to fetch limited record from DB (SQL Server)

    Hi,

    I have 20,000 records in a table, and i want to retrieve only 20- 40 records from the table.How can v specify the range.

    we can achieve this in oracle using the ROWNUM like the follwing query.
    "select * from tblPerson where ROWNUM>20 and ROWNUM<40".

    Is there any way to get the same in SQL Server7.0.

    If possible, please let me know.

    Thanx.
    Kishan

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: How to fetch limited record from DB (SQL Server)

    I don't know if this is exactly what you are looking for, but you can achieve a similar effect using a combination of TOP n and the ORDER BY clause.

    Assume:
    Code:
    CREATE TABLE tblPerson (
        ID int IDENTITY (1,1) NOT NULL,
        LastName varchar(20),
        FirstName varchar(20),
        AddBy varchar(20),
        AddDate datetime
    )
    You can then select the top 20 records using:
    Code:
    SELECT TOP 20 *
    FROM tblPerson
    ORDER BY ID
    Or you can select the bottom 20 records using:
    Code:
    SELECT TOP 20 *
    FROM tblPerson
    ORDER BY ID DESC
    You can also alter the sort field and pick the top 20 by name or add date.

    I think using ADO you can also paginate through a recordset by specifying how many records per page and which page to pull back. I am less familiar with this process, however.


    HTH,

    Hugh Scott
    Originally posted by d_kishan
    Hi,

    I have 20,000 records in a table, and i want to retrieve only 20- 40 records from the table.How can v specify the range.

    we can achieve this in oracle using the ROWNUM like the follwing query.
    "select * from tblPerson where ROWNUM>20 and ROWNUM<40".

    Is there any way to get the same in SQL Server7.0.

    If possible, please let me know.

    Thanx.
    Kishan

  3. #3
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138
    Or you can do it similar to Oracle by checking the value of the uniqueidentifier...assume you have the table structure that hmscott posted... then you could use

    "SELECT * FROM tblPerson WHERE ID>= 20 AND ID <= 40"

    this would pull the records 20 - 40

  4. #4
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    ...or like this...:

    "SELECT * FROM tblPerson WHERE ID BETWEEN 20 AND 40"
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Frettmaestro and Memnoch1207, what happens when there are gaps in the IDs?
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Hmm...you will only get those with ID 20, 40 or something in between meaning that you might get 5 records, 13 or maybe 19 records... if you need to get say 20 records at a time you need to use the TOP-statement:

    Select TOP 20 * FROM table WHERE ID > 20
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  7. #7
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    This one of the most common problems with MSSQL2K. I know 2 solutions

    Code:
    
    --1. Order by key musts be unique
    select         * from 
    (
     select top 10 * from 
     (
     select top 50 * from master.dbo.sysobjects so 
          order by name ASC ,id ASC
     ) x order by name DESC,id DESC
    ) xx order by name ASC ,id ASC
    
    --2. Order by key need not to be unique
    select OrderId=identity(int,1,1),* into #temp666 from master.dbo.sysobjects so order by name,id
    select * from #temp666 where OrderId between 41 and 50 order by OrderId
    drop table #temp666
    
    Last edited by ispaleny; 02-13-03 at 16:01.

  8. #8
    Join Date
    Feb 2003
    Posts
    3

    Re: How to fetch limited record from DB (SQL Server)

    Originally posted by d_kishan
    Hi,

    we can achieve this in oracle using the ROWNUM like the follwing query.
    "select * from tblPerson where ROWNUM>20 and ROWNUM<40".

    Kishan
    Hi d_kishan,
    I think in SQL you can achieve this easily with TOP n, but in Oracle you can't use ROWNUM to make this happen, because ROWNUM is not the order number of the returning rows. It's just the number that indicate the order of entry.

    I still don't know the right way in Oracle yet.

    Have Fun! ^_^

  9. #9
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    To retrieve records between 30 and 50:
    Code:
    SELECT TOP 30
      [SomeFields]
    FROM
      (SELECT TOP 50
          [IDField]
       FROM
          [YourTable]
       ORDER BY
          [IDField]
       DESC
      )
    ORDER BY
       [IDField]
    DESC
    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  10. #10
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Bunce, I won't be nice. You posted very complicated and not functional way how to

    SELECT TOP 30 [SomeFields]
    FROM [YourTable]
    ORDER BY [IDField] DESC

    But I understand your major idea. Read previous posts.

  11. #11
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    HUH? Please read my post before you decide to not 'be nice'.

    My post is the simplest way to retrieve a set of records that are not at the beginning or end of a recordset in SQL SERVER, which is what the poster asked.

    It is NOT advisable to use something like:
    Code:
    SELECT [Fields] FROM [Table] WHERE ID > X AND ID < Y
    because you cannot guarantee that records have not been deleted and therefore break the sequence of the ID field.

    There is no need to look in SysObject or create a Temp table.

    I'm not sure what you are on about???
    Last edited by Bunce; 03-07-03 at 05:06.
    There have been many posts made throughout the world.
    This was one of them.

  12. #12
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    1. I used master.dbo.sysobjects as a test data table only.
    2. Select identity into must be used, when you do not have any candidate key or the key is too large for order by, to return unique data in pages. In this case, the simplest way is to use a cursor and the best way is a change of your table design.
    3. Your code was incorrect

    Code:
    SELECT TOP 30
      [SomeFields]  some fields are without joins somewhere
    FROM
      (SELECT TOP 50
          [IDField]
       FROM
          [YourTable]
       ORDER BY
          [IDField]
       DESC
      ) alias missing
    ORDER BY
       [IDField]
       DESC 2nd order must be reversed
    4. I am going to sleep.
    Last edited by ispaleny; 03-07-03 at 04:33.

  13. #13
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Oops, missed my outer query!
    There have been many posts made throughout the world.
    This was one of them.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •