Limit the number of rows returned in an isql query

[Simon Taylor]

I'd like to learn how to limit the number of rows returned
in an isql query.

In Oracle, (which is the environment I am used to), you can
use the pseudo-column ROWNUM to limit the number of rows
returned, ie:

select column1, column2
from table
where rownum < 10;

I assuming that there is an equivalent construction in Transact SQL.

I've looked in vain through the SQL Server FAQs for the
answer to this question, and I'd be grateful for any pointers.

Regards,

Simon Taylor

[Fredrik Dohlen]

In SQL 7.0 you can use the "select top n" syntax. I don't think SQL 6.5 has
this feature.

Regards,

Fredrik Dohlen

Simon Taylor wrote in message
<01be11ef$c0ad6e80$0264...@simon.iaccess.com.au>...

[Sarbjit Singh Gill]

Hi,,

If you are using the isql/w utility, you could just go to Query menu in
this utility and select the "Set Options" options. There is a text box
for Row Count. That would limit the number of rows that are returned !

I hope this helps

SSGill

[khali...@my-dejanews.com]

Yes you can limit the number of rows in isql by setting the option ROWCOUNT
to <NUMBER> following are two methods to do so:

1. you can run the command SET ROWCOUNT 25 in isql or anywhere in stored
procedure or trigger. To reset the limit you by SET ROWCOUNT 0

2. It can also be done through enterpise manager i. select QUERY from main
menu ii. select SET OPTIONS (last option) iii.you have Query Options page,
at the bottom of the page Limit is set to zero for Row Count iv. you could
set the desired number here

Khalid Latif
MCP SQL Server

In article <01be11ef$c0ad6e80$0264...@simon.iaccess.com.au>,

"Simon Taylor" <no_email...@due.to.spam.com> wrote:
> I'd like to learn how to limit the number of rows returned
> in an isql query.
>
> In Oracle, (which is the environment I am used to), you can
> use the pseudo-column ROWNUM to limit the number of rows
> returned, ie:
>
> select column1, column2
> from table
> where rownum < 10;
>
> I assuming that there is an equivalent construction in Transact SQL.
>
> I've looked in vain through the SQL Server FAQs for the
> answer to this question, and I'd be grateful for any pointers.
>
> Regards,
>
> Simon Taylor
>

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[khali...@my-dejanews.com]

[khali...@my-dejanews.com]

Yes you can limit the number of rows in SQL Server 6.5, you can do it by
Enterprise manager or by isql/w

1. Through Enterprise manager
* Select QUERY from main menu of Enterprise manager
* then select SET OPTIONS
* now you can set the limit for Row Count
* to reset the limit you can put 0 in the ROWCOUNT
2. Through ISQL/W
SET ROWCOUNT 10 <set limit to 10>
SET ROWCOUNT 0 <to cancel limit>

[c81...@my-dejanews.com]

C. ROWCOUNT

ROWCOUNT stops processing after the specified number of rows. In this example,
note that x rows meet the criteria of advances less than or equal to $5,000;
however, from the number of rows returned by the update, you can see that not
all rows were processed. ROWCOUNT affects all Transact-SQL statements.

SELECT Count = count(*)
FROM titles
WHERE advance >= 5000
go

Count
-----------
10

(1 row(s) affected)

SET ROWCOUNT 4
go
UPDATE titles
SET advance = 5000
WHERE advance >= 5000
go
(4 row(s) affected)

This should work.
Regards
Mars.

In article <72rc24$6b$1...@elle.eunet.no>,

"Fredrik Dohlen" <fredrik...@visma.no> wrote:
> In SQL 7.0 you can use the "select top n" syntax. I don't think SQL 6.5 has
> this feature.
>
> Regards,
>
> Fredrik Dohlen
>
> Simon Taylor wrote in message
> <01be11ef$c0ad6e80$0264...@simon.iaccess.com.au>...

> >I'd like to learn how to limit the number of rows returned
> >in an isql query.
> >
> >In Oracle, (which is the environment I am used to), you can
> >use the pseudo-column ROWNUM to limit the number of rows
> >returned, ie:
> >
> > select column1, column2
> > from table
> > where rownum < 10;
> >
> >I assuming that there is an equivalent construction in Transact SQL.
> >
> >I've looked in vain through the SQL Server FAQs for the
> >answer to this question, and I'd be grateful for any pointers.
> >
> >Regards,
> >
> >Simon Taylor
>
>

-----------== Posted via Deja News, The Discussion Network ==----------

[Michael]

In SQL Server 6.5 use SET ROWCOUNT

In SQL Server 7 use TOP command in select statement

Michael
Simon Taylor <no_email...@due.to.spam.com> wrote in message
01be11ef$c0ad6e80$0264...@simon.iaccess.com.au...