Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

limit number of rows in a query

Posted on 2004-04-08
4
Medium Priority
?
31,218 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 180 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 120 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 180 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Ranking ecommerce websites is a vital process. You need to have a strong SEO (Search Engine Optimization) strategy. If you don’t have one, you are losing out on brand impressions, clicks and sales. Check this guide on how to improve website traffic …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

824 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