Re: How to limit # of rows returned by SELECT

Stephen Ellis Lasley wrote:
>
> Dieter Oberkofler wrote:
> >
> > 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
> >
>
> Uh, this is SQL Server. There is no such thing as "rownum".

I guess what he meant above is to have a 'rownum' identity column in the table. But this will work when you have 'rownum < 100' in the where clause. I like the 'set rowcount' option best so far. Received on Wed Feb 28 1996 - 00:00:00 CET