October 24, 2008 at 12:23 pm
Hi,
I've been stuck on this for a while. I had fine working pagination for my search feature using POSTGRESQL and the LIMIT and OFFSET features. Then as I was preparing to move to a new host which uses MS SQL 2005, I learn that Microsoft doesn't have those features!?!? Anyway, I looked around to find an alternative way:
so I came across this:
Here is the template:
SELECT TOP id FROM task_log ORDER BY ID) ORDER BY ID
So, for example, to display 10 items from 51-60 we would do:
SELECT TOP 10 * FROM task_log WHERE id NOT IN (SELECT TOP 50 id FROM task_log ORDER BY ID) ORDER BY ID
So in my case I try to make it work...
and I wrote this:
SELECT TOP(#mr#) productid,model,iconnew
FROM products
WHERE productid NOT IN (SELECT TOP(#sr#-1) * FROM products WHERE (ProductDescription LIKE '#keyword#' or model LIKE '#keyword#' or productid LIKE '#keyword#' or longdescription LIKE '#keyword#') AND inactive = 0)
ORDER BY productid ASC
When I execute this, I keep getting the following error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Not too sure what this means in my case, or hwo to fix this...
Can anyone please help? i've been stuck on this forever and I'm getting close to a deadline...
October 24, 2008 at 1:49 pm
Google should've answered this quickly
http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx
SELECT * FROM
(
SELECT *
, row = ROW_NUMBER() OVER (ORDER BY ID)
FROM task_log
)
WHERE row BETWEEN 51 AND 60
October 24, 2008 at 2:45 pm
thank you! a solution in the comments of singingeels site worked for me!
October 24, 2008 at 3:30 pm
yes, ROW_NUMBER is a very useful and versatile tool that can be used in a number of circumstances.
FYI, the problem in your original query seems to be this line:
WHERE productid NOT IN (SELECT TOP(#sr#-1) * FROM
when you use a subquery with IN, you should only select the column that you are matching to productid in this case, not SELECT *
October 24, 2008 at 3:48 pm
ah....
so if I need more data, what would i do?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply