Re: How to limit # of rows returned by SELECT

Jason Lincoln wrote:
>
> In your select use the constraint where rownum < some_number
>
> In article <Pine.SOL.3.91.960208160943.3865C-100000_at_mail>,
> hrothenb_at_mail.bcpl.lib.md.us says...
> >
> >Before the select use the
> > set rowcount 50
> >command.
> >
> >On Thu, 8 Feb 1996, Screemin! wrote:
> >
> >>
> >> Does anybody know if there is a way to limit the number of records
> >> returned by a SELECT statement? I have a large ODBC database
 

> >> I tried SELECT *, COUNT(*) FROM TABLE WHERE COUNT(*) <=50
> >> but of course this didn't work, nor should it logically.

The only working method to restrict the # of rows to fetch is to use the rownum virtual column. (eg. select * from table where rownum < 100). This works fine if you do not use other where restrictions or a order by clause.
The where (eg. rownum < 100) is evaluated before the order by and select the first 100 rows in the internal order of the table and not the one specified in the order by clause.

Dieter Oberkofler

LEADING BITS GmbH.               Tel             (+43-1) 586 76 11
Schleifmuehlgasse 5/17           Fax             (+43-1) 587 76 15
A-1040 Vienna                    E-mail     dtr_at_leadingbits.via.at
Austria                          Compuserve            100141.1314
------------------------------------------------------------------