Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: Select only certain number of records

    I have an app where I need to select only a certain number of records. My app is going to be "paginated" meaning that it would be similar to a forum. Let's use a forum as an example. I have a thread that has 4000 posts in it. On page one, I want posts 1-20. On page two, I want 21-40. On page three, I want 41-60 and so one. Is there a way to make a query that would do this? I can have a variable in my app that will keep track of the last number so if I were on page two, the last number would be 40. Thanks, Jeremy
    Nothing better than a good ride.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    You can take help of SELECT TOP .... and refer to books online for more information on this statement.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    JCScoobyRS if your posts are sequentialy numbered you could use the between function in your where clause.

    select * from <your table> where postid between 41 and 60.

    I wrote a stored procedure for a friend to do much the same, he passed the number of posts on a page and either the last post displayed or the page number to be displayed. The math is simple after that.
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    They aren't since this isn't a forum. This app functions similarly to a forum but is not a forum. Is there anyway to implement a stored procedure? Anyone got any ideas that might help me out? Thanks, Jeremy
    Nothing better than a good ride.

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    If you post a few more detail about your table(s) like relationships and keys I am sure someone can get you going.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    The funny thing is that there is no relationship that I'd be able to do this with or hopefully I would had though of it. What I'm basically trying to do is get a count of rows that will be returned from any query, let's say 4000 rows, and then display only a certain portion of the results, like records 41-60. Does that make better sense? Thanks, Jeremy
    Nothing better than a good ride.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This type of functionality really belongs in the interface and not on the Database Server. The problem is that the database server is stateless, and doesn't know what it has returned previously and what it has not. So without a field value to order by that you can pass to the procedure as a parameter this becomes quite awkard.

    Surely you are returning the dataset in some sort of order, or the table has an index on it? If not, you don't have any gaurantee that event returning the first 10 records twice in a row will result in the same dataset.

    Now, awkward does not mean impossible. If your interface maintains a connection to the database, you could store the results of the query in a temporary table with a sequential index and then just peel off a page worth of records at a time for subsequent calls.

    blindman

  8. #8
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    The whole point is to keep from having to query all 4000 results. I could store it in a dataset BUT since web apps are stateless, how could I do that? Thanks, Jeremy
    Nothing better than a good ride.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If your table contains something like a record id or a unique row identifier, then it's simple, especially if it is numeric. You can also look into SET ROWCOUNT statement.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I'll look into the "SET ROWCOUNT" idea but there is nothing in these rows that would give me the ability to do:

    SELECT * FROM TABLE WHERE COLUMN BETWEEN X AND Y

    If so, it would be very simple but it's not the case here. Thanks alot, Jeremy
    Nothing better than a good ride.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can always use something like this providing you have a unique row identifier field:

    create proc dbo.sp_GetNthNumberOfObjectNames (
    @start int,
    @end int )
    as
    if @start is null set @start = 1
    if @end is null set @end = @start + 19
    if (@end - @start) != 19 set @end = @start + 19
    select cnt, name from (
    select cnt=count(s2.name)+1, s1.name from sysobjects s1
    left outer join sysobjects s2
    on s1.name > s2.name
    group by s1.name) x
    where cnt between @start and @end
    return (0)

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are not going to be able to solve this problem until you add a column which allows you to do
    SELECT * FROM TABLE WHERE COLUMN BETWEEN X AND Y

    blindman

  13. #13
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    That's the problem though. I don't have a unique row identifier. If I did, I could use a between statement in my where clause.
    Nothing better than a good ride.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Any way to ALTER TABLE and ADD RowID int IDENTITY(1,1) NOT NULL?

  15. #15
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Okay. That is the result I was thinking I'd get. I didn't know if it were possible and since Microsoft tends to steer from standards and add proprietary functions, I just thought there might be one for that. Laters, Jeremy
    Nothing better than a good ride.

Posting Permissions

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