Solved

limit number of rows in a query

Posted on 2004-04-08
4
31,181 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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

This article outlines the process to identify and resolve account lockout in an Active Directory environment.
Google always has something new and amazing up its sleeve, and the most current thing that they have been working on is another step in the evolution of Google Search, from machine learning to its brilliant successor, deep learning.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

832 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