Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: First 20 records only
I agree it makes no sense but in response to a similar question in february Jonathan wrote
You may be lucky with your point version of 7.3.4, some of them have acquired the ability to include an ORDER BY inside a view, so try the following:
select id, column_to_sort, rownum
from (
select id, column_to_sort
from my_table
order by column_to_sort desc
)
where rownum <= 30
;
If Oracle allows this, and doesn't complain about a missing right parenthesis, then you can try.
delete from my_table where id not in (
select id from (
select id, column_to_sort, rownum
from ( select id, column_to_sort from my_table order by column_to_sort desc
So it has to be worth at least trying.
-- Niall Litchfield Oracle DBA Audit Commission UK "Mark J. Bobak" <mark.NOSPAM_at_bobak.net> wrote in message news:20010610.030802.1244316437.29713_at_bobak.net...Received on Mon Jun 11 2001 - 06:06:20 CDT
> In article <3b21ef8f$0$12246$cc9e4d1f_at_news.dial.pipex.com>, "Niall
> Litchfield" <niall.litchfield_at_dial.pipex.com> wrote:
>
> > "Jack" <No_at_Mail.Please> wrote in message
> > news:0n2U6.69731$662.285365_at_news1-hme0...
> >> Hi
> >> Can you please tell me how to 'select' only (the first) 20 records of a
> >> table?
> >> is it ;
> >> select * from tableA sample (20)
> >
> > various people have given you the where rownum < 21 answer. This is
> > absolutely fine if you only want 20 records and do not care which they
> > are. If you do care which they are (the top 20 sales for example)
> > you'll need to look on deja for the answers in this group to the
> > equivalent of TOP. 8i and above the syntax is (from memory)
> >
> > select * from (select ... from table order by key_column desc) where
> > rownum < 21;
> >
> > I believe that Jonathan Lewis has pointed out that order by in an inline
> > view has got back-ported to some patch releases of 7.3 as well.
>
> Backported to 7.3??? Are you kidding? It was an 8i feature, not
> backported to 8.0, but it got into 8i??
>
> That makes no sense at all to me.
>
> -Mark
![]() |
![]() |