• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 31224
  • Last Modified:

limit number of rows in a query

Is it possible to limit the number of rows returned in a query?


If I use set rowcount 10, will it set the limit for just my connection ?


With MSSQL I can use select top n,
 

Thanks
0
NorBro
Asked:
NorBro
  • 2
3 Solutions
 
william_jwdCommented:
try

where rownum < 11
0
 
namasi_navaretnamCommented:
Try,

SET ROWCOUNT 10

select * from MyTable

Top 10 records will be returned

Regards-

0
 
bretCommented:


SET ROWCOUNT is the simplest way to limit the number of rows in the result set.  It only applies to your connection.  Don't forget to SET ROWCOUNT 0 to reset the option to allow all rows.

There is no "rownum" by default in ASE, though another approach is to select the result set plus an identity column into a temp table and then use the identity column as a rownum to select a subset of the result set.  Example:

select *, identity(9) as "rownum"
  into #foobar
  from sysindexes
select id, name from #foobar
  where rownum between 22 and 42
drop table #foobar
0
 
namasi_navaretnamCommented:
SET ROWCOUNT is session based. Only valid on the connection you establish. Need to SET ROWCOUNT back to 0 as Bret suggests. Better to create a sp that runs the select as it may be easier to maintain.

Create Procedure sp_Test
As
Begin
SET ROWOUNT 10

select * from MyTable

SET ROWCOUNT 0

END



0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now