May 23, 2007 at 2:48 pm
Hi,
I am working on this select query for a report on website users. The resulting rows will be displayed in a datagrid with custom paging. I want to fetch 100 rows each time. This is the simplified query, @currpage is passed as a parameter.
________________________________________________________________________________
DECLARE @table TABLE (rowid INT IDENTITY(1,1), userid INT)
INSERT INTO @table (userid) SELECT userid FROM Users
SELECT T.rowid, T.userid, ISNULL(O.userid, 0)
FROM @table T
LEFT OUTER JOIN
(
SELECT DISTINCT(userid) FROM orders
)
AS O
ON O.userid = T.userid
AND T.rowid > ((@currpage-1) * 100)
AND T.rowid <= (@currpage * 100)
ORDER BY T.rowid
________________________________________________________________________________
If I run this query it returns all the rows, not just the 100 rows corresponding to the @currpage value. What am I doing wrong?
(The second table with left outer join is there as I need one field to indicate whether the user has placed an order with us or not. If the value is 0, the user has not placed any orders)
May 23, 2007 at 3:14 pm
am not sure about the query you are using, but when navigations required from one page to another, have another column in the table with auto increment, then when you run the query, you can pass the page number.
eg. if the pagenumber is 2, then you can filter identity column bewteen 101 and 200. (assuming 100 records per page), This can also be configured by passing another parameter.
May 23, 2007 at 3:25 pm
I don't think it will work in this case. This page has a dropdown option for the users to order the resultset on different fields.
May 23, 2007 at 8:53 pm
Actually you are limiting to 100 records from Users, not from Orders.
So, what you get is all orders for 100 selected users.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply