Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: First 20 records only

Re: First 20 records only

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 11 Jun 2001 12:06:20 +0100
Message-ID: <3b24a73a$0$12241$ed9e5944@reading.news.pipex.net>

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

    )
    where rownum <= 30
)
;

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...

> 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
Received on Mon Jun 11 2001 - 06:06:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US