Re: Help with ROW_COUNT()

In either case, I need where row_count = 6 . How do I get that?

Thanks.

• Scott

Alexander Medvedev wrote:

> Hi Niall,
>
> Niall Litchfield wrote:
>
> > you don't state version but assuming 8i how about
> >
> > select pkey,value from
> > (select pkey,value from table1 where value is not null order by value asc)
> > where rownum < 6;
> >
> > prior to 8i the order by can't be included in the subquery.
>
> You may use in Oracle earlier versions a distinct instead of order by:
>
> select pkey,value from (select distinct pkey,value from table1 where value is
> not null)) where rownum < 6;
>
> Regards Alexander Medvedev
>
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> > <sw_at_weinerfamily.org> wrote in message
> > news:3A2BB41B.12EA754D_at_weinerfamily.org...
> > > I need to find the 5th row in a selection, ordered by a value column.
> > > Since ROWNUM takes affect after the rownum is assigned it won't work.
> > > So I tried using ROW_NUMBER(). However the following query doesn't
> > > work:
> > >
> > > SELECT pkey, value,
> > > ROW_NUMBER() over (ORDER BY value ASC NULLS LAST) AS valcol
> > > FROM table1
> > > WHERE valcol = 3
> > >
> > >
> > >
Received on Fri Dec 08 2000 - 20:21:11 CET