Re: How to Retrieve particular rows ?.

Problem is that Order cannot be in subQuery. But you can use Group by(in subquery) insted. Group by all fields so you dont have to use Max, or other agg. functions. This should work.

Edo

Raj wrote:

> it's not accepting Order by clause in sub-query. says " ) missing " . When i
> remove the Order by clause it works.
>
> Paul Druker wrote:
>
> > What error are you getting?
> >
> > I habe Oracle 8.1.5, and it's working fine.
> >
> > "Raj" <rajarulmani_at_att.com> wrote in message
> > news:38EB33E8.5CD00C5C_at_att.com...
> > > Paul,
> > > Thanks for your response ,
> > > I tried the query and it's giving an eror with "order by" clause in
> > 8.0.5 .
> > >
> > >
> > > Paul Druker wrote:
> > >
> > > > Try:
> > > >
> > > > select coll , rownum from (
> > > > select * from table1 where
> > > > col1 <= 'some value1 ' and
> > > > col2 = 'some value2'
> > > > order by col1 desc)
> > > > where rownum <= 10
> > > >
> > > > Regards,
> > > > Paul
> > > >
> > > > "Raj" <rajarulmani_at_att.com> wrote in message
> > > > news:38EA6880.BAC94B8B_at_att.com...
> > > > > Can any one help me ?.
> > > > >
> > > > > I have problem with the following query :
> > > > >
> > > > > select coll , rownum from table1 where
> > > > > col1 <= 'some value1 ' and
> > > > > col2 = 'some value2' and
> > > > > rownum <= 10
> > > > > order by col1 desc.
> > > > >
> > > > >
> > > > > Both col1 and col2 are the primary keys of table table1, and there is
> > > > > also and index on both the columns.
> > > > >
> > > > > The query returns first 10 rows always ( irrespective of the " col1
> > <=
> > > > > 'some value1 ' " ) , which is not i expected. I need 10 rows starting
> > > > > from 'some value1' in desc order and rownum from 1 to 10.
> > > > >
> > > > >
> > > > > Thanks,
> > > > > Raj.
> > > > >
> > > > >
> > >
Received on Fri Apr 07 2000 - 06:31:30 CDT