www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 28

Thread: paging...

  1. #1
    Join Date
    May 2003
    Posts
    37

    paging...

    my "forums posts" table contains thousands of thread in each subject. I want to show them 30, 30 (simple paging). It's look to me a great waste to load the full dataset each time and then to show rows 1-30, 31-60 and so…

    Are there is a way to full the dataset only with the current values (if I use a page list ([1] [2] [3]… and so?)?

  2. #2
    Join Date
    Nov 2003
    Posts
    655
    SELECT TOP 30 ...

    Edit: though you'll also need to include a value in the WHERE clause that'll exclude records in previous pages.

    If you use "id" as your primary key for threads and it auto-increments, then you could use WHERE id < num AND ...
    Set num = maximum positive number to get the first page, set it to the value of the id of the 30th record to get the 2nd page, etc.
    Last edited by CardboardHammer; 08-03-2004 at 02:18 PM.

  3. #3
    Join Date
    May 2003
    Posts
    37
    yes,

    but the other 30, and the other...

  4. #4
    Join Date
    Nov 2003
    Posts
    655
    Guess I wasn't quick enough on the edit... lol

  5. #5
    Join Date
    Nov 2003
    Posts
    655
    You can also do a join of two selects, where one select gets all the records before ones you want on the page and the other gets all the records up to and including the ones you want and then exclude all the records where the primary key of the smaller select is non-null using the WHERE clause.

  6. #6
    Join Date
    May 2003
    Posts
    37
    o.k.

    sorry for the interaption.

    the problem is the id is the key, but the thread sorted by "updateddate" column. so, if some one response to a previous id, it should be shown first...

  7. #7
    Join Date
    Nov 2003
    Posts
    655
    The joined selects would do the trick, ordering all 3 selects by "updateddate".

  8. #8
    Join Date
    Nov 2003
    Posts
    655
    SELECT L.* FROM
    (SELECT TOP 60 * FROM THREADS ORDER BY updateddate) L
    LEFT JOIN
    (SELECT TOP 30 * FROM THREADS ORDER BY updateddate) R
    ON L.id = R.id
    WHERE R.id IS NULL
    ORDER BY L.updateddate

    EDIT: Note that you should actually specify the columns you want rather than using *

  9. #9
    Join Date
    May 2003
    Posts
    37
    I didn't understood it...

    can you p l e a s e explain more the idea?

  10. #10
    Join Date
    Nov 2003
    Posts
    655
    Say you want the 2nd page. That's records 31-60 (not their id, their order). Ask for records 1 - 60 (TOP 60) and also for records 1-30 (TOP 30). Join them on the primary key. Since it's a self-join, you have to use an alias for each side of the join (L and R). Exclude the records that you'd throw away (1 - 30) if you were working with the full recordset (WHERE R.id IS NULL).

    What you end up doing is asking for what you'd throw away, matching to what you'd throw away + what you want, and throwing away those matches, being left with what you want.

  11. #11
    Join Date
    May 2003
    Posts
    37
    so, if i want 120 to 150, i"l do that:
    SELECT L.* FROM
    (SELECT TOP 150 * FROM THREADS ORDER BY updateddate) L
    LEFT JOIN
    (SELECT TOP 120 * FROM THREADS ORDER BY updateddate) R
    ON L.id = R.id
    WHERE R.id IS NULL
    ORDER BY L.updateddate ??

  12. #12
    Join Date
    Nov 2003
    Posts
    655
    Well, that'll get you 121 - 150, but that's what you want (30 records per page).

    But, for best performance, don't use *. In fact, for R (the set of throwaways), all you'd need to select is the id.

  13. #13
    Join Date
    Nov 2003
    Posts
    655
    And another thing, in the L and R queries, you'll likely be throwing in some additional conditions (to only get threads in a certain category, etc.). The WHERE clause needs to be the same L and R, but the the outer WHERE clause only needs to filter out the matches between L and R.

  14. #14
    Join Date
    Nov 2003
    Posts
    655
    EDIT: Double oops! I was right the first time.

  15. #15
    Join Date
    May 2003
    Posts
    37
    o.k. i"l try it.

    the problem is I thought I knew something about programing... (well, sql is different).

    thanks a lot for now.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles