Limiting returned rows

[Krucifyx]

I've looked around to see how to limit the rows returned from a query and I've come across TOP etc., but it's not what I'm looking for.

With MySQL you can use LIMIT start,limit - so for example I'm browsing an archive of pages with 10 items per page, and I'm viewing page 4, it would be "LIMIT 30,10"

With MS SQL, I cannot figure out how to do this. The TOP command only allows me to grab the top X number of rows, but what if I don't want just the top?

So if a query has 75 rows that match the where clause etc., but I only want rows 25-35, how would I formulate that?

 

[tlbroadbent]

In SQL Server, use SET ROWCOUNT 10. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Krucifyx]

Ok, but that only return the first 10 rows, what if I wanted rows 11-20?

 

[lardmonster]

SET ROWCOUNT 10

Not very standard SQL either...

OK, lets think about this:

you want a set of data - let's say something like:

select c.co_name from company c order by c.co_name asc

now lets add another column to get a row-count:
select c.co_name,
(select count(*) from company c2 where c.co_name > c2.co_name)+1
from company c

then we can move this extra column into the 'where' condition, and filter using it, so to get rows 25 to 35, use:

select c.co_name,
from company c
where (select count(*) from company c2 where c.co_name > c2.co_name)+1>=25 and
(select count(*) from company c2 where c.co_name > c2.co_name)+1<=35


is that what you're after?

Me, I'd probably make a SQL view:

create view lardbucket as
select c.co_name,
rowcount=(select count(*) from company c2 where c.co_name > c2.co_name)+1
from company c

select lb.co_name
from lardbucket lb
where lb.rowcount>=25 and
lb.rowcount<=35

Cheers,
Matthew

--
lardmonster - (very) occasional visitor
mkflint@hotmail.com

 

[Krucifyx]

Wow, that's a lot of work for something that should be otherwise rather simple to pull off. In MySQL it would be:

select * from company order by co_name limit 25,10

You'd think this would be a common enough thing that mssql would have something similiar.

 

[lardmonster]

I suppose it is a lot of work, but I guess that's the trade-off with writing code that is implementation-independent.

For example, the select ... top n directive is only valid for MS SQL version >=7.0 --
lardmonster - an occasional visitor
mkflint@hotmail.com