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