How to replicate mysql range and limit in SQL Server using the top clause

I've been trying to replicate the limit and range feature provided in MySql in SQL Server with no luck as of yet. I have found several guides and now think my sql code is nearly correct but I'm still getting an error posted below.

System.Data.SqlClient.SqlException: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

The error code says to use EXISTS but i have tried that instead of NOT IN a开发者_运维知识库nd i still get an error.

My sql is posted below

SELECT TOP (@range) * 
FROM client 
WHERE clientId NOT IN 
      (SELECT TOP (@limit) * 
       FROM client 
       ORDER BY clientId) 
ORDER BY clientId

The change you need to make to your code is

SELECT TOP (@range) *
FROM   client
WHERE  clientId NOT IN (SELECT TOP (@limit) clientId /*<-- NOT "*" here */
                        FROM   client
                        ORDER  BY clientId)
ORDER  BY clientId  

This can also be done by using row_number as below (which performs better depends on the different indexes available and how wide a covering index on the whole query is compared to a narrow one on just clientId.)

DECLARE @lowerlimit int
SET @lowerlimit = @range +@limit;

WITH cte As
(
SELECT TOP (@lowerlimit) * , ROW_NUMBER() OVER (ORDER BY clientId) AS RN
FROM client
ORDER BY clientId
)
SELECT * /*TODO: Your Actual column list*/
FROM cte 
WHERE RN >= @limit

Another (similar, slower :) ) way

SELECT * FROM (       
    select rank() over (ORDER BY yourorder) as rank, *.X
                from TableX X
) x2 WHERE rank between 5 and 10