Limiting Result Set 
Author Message
 Limiting Result Set
I hope this isn't an FAQ (BTW: where is the FAQ?), but I can't find in the books. I want to issue a select statement (it doesn't matter what it is) and limit the output to the first n rows. In another SQL I was able to do this:

select ...
where ...
and count <= 10

Is there any way to achieve a similar limiting of the result set in Sybase?

Thanks,
- Bill



Sat, 01 Feb 1997 04:24:29 GMT
 Limiting Result Set

Quote:

> I hope this isn't an FAQ (BTW: where is the FAQ?), but I can't find in the books. I want to issue a select statement (it doesn't matter what it is) and limit the output to the first n rows. In another SQL I was able to do this:

> select ...
> where ...
> and count <= 10

> Is there any way to achieve a similar limiting of the result set in Sybase?

> Thanks,
> - Bill

FAQ: available through Mosaic:
        http://arch-http.hq.eso.org/bfrasmus/db/faq/index.html

To answer your specific question try this:

set rowcount #ofrows

select ...
where ...

All the best.

Olly

Allwyn Lobo

Flex Fields :"Just define a Rule.." - Its the wave of the future.



Sat, 01 Feb 1997 04:50:53 GMT
 Limiting Result Set
Quote:

>I hope this isn't an FAQ (BTW: where is the FAQ?), but I can't find in the

books. I want to issue a select statement (it doesn't matter what it is) and
limit the output to the first n rows. In another SQL I was able to do this:

Quote:

>select ...
>where ...
>and count <= 10

>Is there any way to achieve a similar limiting of the result set in Sybase?

>Thanks,
>- Bill

set rowcount 10
select * from authors

This query will return you the first 10 rows affected. Don't forget to reset the
rowcount to 0. Resetting it to 0 will return all rows affected (this is the
default).
_______________________________________________________

David D. Boscia
_______________________________________________________



Sat, 01 Feb 1997 19:41:54 GMT
 Limiting Result Set

Quote:

>I hope this isn't an FAQ (BTW: where is the FAQ?), but I can't find in the books. I want to issue a select statement (it doesn't matter what it is) and limit the output to the first n rows. In another SQL I was able to do this:

>select ...
>where ...
>and count <= 10

>Is there any way to achieve a similar limiting of the result set in Sybase?

Sure -

SET ROWCOUNT n
.
.
SELECT ...
FROM ...
WHERE ....

Quote:
>Thanks,
>- Bill

Regards
David S.

+-----------------------------------------------------+
| The Truth is Out There.                             |
| I know this as that's where I was when I lost it... |
+-----------------------------------------------------+



Sun, 02 Feb 1997 08:50:01 GMT
 Limiting Result Set

I would like to select out the rows such that the sum of these rows is
less than some upper limit.  Is this possible in SQL?

select ...
from ...
where ...
  and sum(x) <= LIMIT  /* Select while the sum(x) is <= LIMIT */
order by date, ...

This might be used, for example, to determine how long it took to
make $100.00.

-Mike



Mon, 03 Feb 1997 21:59:06 GMT
 Limiting Result Set

|>
|> I would like to select out the rows such that the sum of these rows is
|> less than some upper limit.  Is this possible in SQL?
|>
|> select ...
|> from ...
|> where ...
|>   and sum(x) <= LIMIT  /* Select while the sum(x) is <= LIMIT */
|> order by date, ...
|>
|> This might be used, for example, to determine how long it took to
|> make $100.00.
|>
|> -Mike

Try the following:

SELECT col1, col2, SUM(col3)
FROM   tablename
ORDER BY col1, col2
HAVING SUM(col3) <= SOME_LIMIT

                                Teresa Larson

+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
| Teresa A. Larson - Hughes STX Corporation                            |
| NASA/GSFC Code 933.0                        voice:  (301) 286-7867   |
| Greenbelt, Maryland  20771                  fax:    (301) 286-1777   |

+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+



Tue, 04 Feb 1997 21:40:39 GMT
 Limiting Result Set

|> I hope this isn't an FAQ (BTW: where is the FAQ?), but I can't find in the books. I want to issue a select statement (it doesn't matter what it is) and limit the output to the first n rows. In another SQL I was able to do this:
|>
|> select ...
|> where ...
|> and count <= 10
|>
|> Is there any way to achieve a similar limiting of the result set in Sybase?
|>
|> Thanks,
|> - Bill
|>
|>
|>
|>
|>
Use the set rowcount command

set rowcount n
go
select .....
go
set rowcount 0  /* reset to return all rows */
go

Lenny



Sun, 02 Feb 1997 04:34:49 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Way to limit result set size?

2. *** LIMITING RESULTS SET SIZE ****

3. Transact-SQL and Limiting Result Sets

4. Limiting Result Sets with SQL 7/VB 6

5. Limiting Result Set Size ?

6. Limiting result set size to arbitrary number

7. Query regarding SQL Query - Limiting Result Set

8. limiting result set size

9. Limiting result sets from the server

10. Cursor result set limit of 1500?

11. size limit of result set on remote procedure calls

12. limiting field header size in result set


 
Powered by phpBB® Forum Software