Solved

limit number of rows in a query

Posted on 2004-04-08
4
31,203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Today’s effective marketing is about coming down to the customers’ level and engaging in a whole new way. A text message is one of the most effective and influential ways that you can engage your customers. Here are eight ways that you can utilize t…
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

695 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