limiting the range of select result

Hi,

i'm searching how to tell to mysql server to limit
the range of a result select.
I would like to have the equivalent of the LIMIT clause in MYSQL.

I know there is TOP but it is not sufficient because it returns
only the first n lines.
I would like to tell sqlserver to begin at a certain offset.

Any idea ?

Thxyou can use a derived table

select top 10 *
from (select top 20 * from tbl order by id) as a
order by id desc

Will get the second 10 recs.

limiting query result based on AS clause

can some point me to a sql statement that would perfrom the equivalent of th
e
following statement?
select Desc, (select count (*) from CART_Items where
fkItemCategoryID=CART_ItemCategories.pkItemCategoryID ) itemcount
from CART_ItemCategories
where itemcount >0
typical ouput
Desc itemcount
Cat 1 0
Cat 2 2
Cat 3 1
Cat 4 0
i would simply like to limit my results to itemcount values greater than zer
o.SELECT
ic.Desc,
COUNT(ci.fkItemCategoryID)
FROM
Cart_Items ci
INNER JOIN
Cart_ItemCategories ic
ON
ci.fkItemCategoryID = ic.pkItemCategoryID
GROUP BY
ic.Desc
HAVING
COUNT(ci.fkItemCategoryID) > 0;
"Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
news:FFF7450A-A17A-479B-B169-8DE92212E3CD@.microsoft.com...
> can some point me to a sql statement that would perfrom the equivalent of
> the
> following statement?
> select Desc, (select count (*) from CART_Items where
> fkItemCategoryID=CART_ItemCategories.pkItemCategoryID ) itemcount
> from CART_ItemCategories
> where itemcount >0
>
> typical ouput
> Desc itemcount
> Cat 1 0
> Cat 2 2
> Cat 3 1
> Cat 4 0
> i would simply like to limit my results to itemcount values greater than
> zero.|||thanks a million
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT
> ic.Desc,
> COUNT(ci.fkItemCategoryID)
> FROM
> Cart_Items ci
> INNER JOIN
> Cart_ItemCategories ic
> ON
> ci.fkItemCategoryID = ic.pkItemCategoryID
> GROUP BY
> ic.Desc
> HAVING
> COUNT(ci.fkItemCategoryID) > 0;
>
>
> "Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
> news:FFF7450A-A17A-479B-B169-8DE92212E3CD@.microsoft.com...
>
>

LIMIT rows with an offset

I have a query in MySQL which needs to be translated to an equivalent in MSSQL Server. The query uses a LIMIT clause with an offset.

select * from test LIMIT 10,5

(meaning from the 10th row containing 5 rows in the result set)

How can we port the same logic to SQL Server ? I know TOP can retrieve top n rows but how can we specify the offset ?

Any help is appreciated !Any such offsets or paging is meaningless without an ORDER BY clause.|||Blindman is quite correct, without an ORDER BY clause this is dangerous in any SQL dialect.

SQL-2000 doesn't have a good way to implement the functionality of MySQL's LIMIT clause. Until a new version of SQL provides a better solution, the best answer I can give you is that your code needs to provide the paging functionality.

The best way that I know to provide paging functionality is to use ORDER BY with a "rowset key" within your data and the TOP N syntax to limit the number of rows returned by a SELECT statement. Keep track of the rowset key of the last row returned, and use that as the seed for the next iteration (page) of your SELECT.

-PatP|||Appreciate your help ! Thanks !|||It must be paging day

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx