Solved

limit number of rows in a query

Posted on 2004-04-08
4
31,187 Views
Last Modified: 2012-06-27
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
Comment
Question by:NorBro
  • 2
4 Comments
 
LVL 8

Expert Comment

by:william_jwd
ID: 10784754
try

where rownum < 11
0
 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 60 total points
ID: 10789490
Try,

SET ROWCOUNT 10

select * from MyTable

Top 10 records will be returned

Regards-

0
 
LVL 10

Assisted Solution

by:bret
bret earned 40 total points
ID: 10806124


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
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 60 total points
ID: 10807221
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Advantage DB snapshots, replication or mirroring 2 424
Sybase SQL Anywhere ISQL 8 1,009
SQL Query 2 341
MSSQL Error: 9002, Severity: 17, State: 2. MSQLSERVER Process shuts off 9 515
This article explains the steps required to use the default Photos screensaver to display branding/corporate images
Most MSPs worth their salt are already offering cybersecurity to their customers. But cybersecurity as a service is wide encompassing and can mean many things.  So where are MSPs falling in this spectrum?
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question