limit rows returned in query?

limit rows returned in query?

Post by Stephany McMorro » Wed, 04 Oct 2000 04:00:00



I can't remember the way to limit the number of rows returned in a select
statement.  If you can refresh my memory, thanks!!

Stephany

 
 
 

limit rows returned in query?

Post by Adi Coh » Wed, 04 Oct 2000 04:00:00


In sql server 7 you can use the top key words

select top 10 * from MyTable order by FieldName

In sql 6.5  you have to use the set rowcount X  (where X is the number of
the rows).  Of course you can also use it in sql server 7.   When you want
to turn the limit off you need to set rowcount to 0.

Adi

Quote:> I can't remember the way to limit the number of rows returned in a select
> statement.  If you can refresh my memory, thanks!!

> Stephany


 
 
 

limit rows returned in query?

Post by Stephany McMorro » Wed, 04 Oct 2000 04:00:00


I  Can't get top to work in sql server 7.0.  Could this syntax be slightly
off?


> In sql server 7 you can use the top key words

> select top 10 * from MyTable order by FieldName

> In sql 6.5  you have to use the set rowcount X  (where X is the number of
> the rows).  Of course you can also use it in sql server 7.   When you want
> to turn the limit off you need to set rowcount to 0.

> Adi


> > I can't remember the way to limit the number of rows returned in a
select
> > statement.  If you can refresh my memory, thanks!!

> > Stephany

 
 
 

limit rows returned in query?

Post by Jens K Jense » Wed, 04 Oct 2000 04:00:00


Syntax is correct, but your database may be in 6X compatibility mode ?
Check out sp_dbcmptlevel myDatabase

regards
jensk


> I  Can't get top to work in sql server 7.0.  Could this syntax be slightly
> off?



> > In sql server 7 you can use the top key words

> > select top 10 * from MyTable order by FieldName

> > In sql 6.5  you have to use the set rowcount X  (where X is the number
of
> > the rows).  Of course you can also use it in sql server 7.   When you
want
> > to turn the limit off you need to set rowcount to 0.

> > Adi


> > > I can't remember the way to limit the number of rows returned in a
> select
> > > statement.  If you can refresh my memory, thanks!!

> > > Stephany

 
 
 

limit rows returned in query?

Post by Neil Pik » Wed, 04 Oct 2000 04:00:00


Stephany,

SET ROWCOUNT xxx

Or use the TOP command in SQL 7 and above

Quote:> I can't remember the way to limit the number of rows returned in a select
> statement.  If you can refresh my memory, thanks!!

> Stephany

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq