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 (  … and so?)?
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.
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.
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.
so, if i want 120 to 150, i"l do that:
SELECT L.* FROM
(SELECT TOP 150 * FROM THREADS ORDER BY updateddate) L
(SELECT TOP 120 * FROM THREADS ORDER BY updateddate) R
ON L.id = R.id
WHERE R.id IS NULL
ORDER BY L.updateddate ??
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.