Re: SQL Problem

What you suggest will not work. Perhaps you should test this before you recommend it to someone.

Quoted from 'ORACLE8 The Complete Reference" p 415:

    "...it's important to understand that this number [rownum]

     is attached to a row as it is first pulled from the database,
     *before* ORACLE executes any order by you've given it."

Not to mention Oracle uses a rolling count operation (either COUNT or COUNT STOPKEY) to provide the rownum. Therefore you cannot use "where rownum=200" in a query. It must be "where rownum < x"

Also you order by worth in your query when you should be ordering by worth "DESC" to go from greatest wealth to least wealth.

There are ways around this although. You can use a view:

Create or replace view worth_vw as (
select name, 1/worth "inverse_worth"
from fortune_500
group BY 1/worth, name );

Then use:
select name from worth_vw
group by rownum,name
having rownum=2;

Challenge: can anyone come up with a more elegant solution?

-Frank

In article <35acaaf0.153601547_at_news1.fast.net>,   Markz_at_starnet.lenfest.com wrote:
> However, if your problem is to , for example, to find the 200th
> richest man from a 'fortune_500' table :
>
> fortune_500 :
> name VARCHAR2
> worth NUMBER
> you would
>
> select name from fortune_500
> where ROWNUM = 200
> ORDER BY worth;
>
> Note ROWNUM, a dummy column representing the
> ordinal of the row AND that I am ordering by worth.
> ORDER BY is important as it eliminates the randomness
> to which Mr. Larsen (correctly) alludes.
>
> On Thu, 09 Jul 1998 08:17:06 -0400, "Peter H. Larsen"
> <plarsen.nospam_at_dc.dynares.com> wrote:
>
> >Hi Elena,
> >Just to "confuse" you a little, there is no such thing as the 500th row
> >in a relational database. At least not an identified 500th row. The
> >sequence of how rows are stored is "random" and not guranteed by Oracle.
> >Hence, you can experience getting different rows if you just query for
> >the 500th row (Oracle only shuffle rows when you alter/insert/delete
> >data).
> >
> >If you number the rows yourself, for instance as a primary key, you can
> >easaly get to key value 500, as you would normally do. Oracle does
> >provide a number of row counter, called NUMROWS - but as I stated
> >above, you'll not have any guarentee which record this one fetches.
> >
> >The confusion is, that a table is the same a file. But it's not! A table
> >does not have any order in the way data is stored internally (of course
> >there is an order ... but it can be changed at any time without notice).
> >
> >- Peter H. Larsen
> >
> >
> >Elena Sotelo wrote:
> >>
> >> Hi, all!
> >>
> >> I have a problem with SQL SELECT. I want to do a SELECT of a
> >> determinate row, by example, "select ...row(500)... from table".
> >>
> >> Can I do this?? How??
> >>
> >> I know that rowid exists, but it is a number very stranger and I not
> >> understand it.
> >>
> >> Can anybody help me???
> >>
> >> PD: I works in Livewire at UNIX platform and Oracle database. Does
> >> anyone knows if in Livewire this is posible??
> >>
> >> Thanks in advance!!!
> >>
> >> --
> >> Elena Sotelo
> >> mailto:esotelo_at_ipmultimedia.es
> >>
> >> IP Multimedia, S.L.
> >> Marina, 16-18 Pl. 18
> >> Telf: (93) 225 99 80
> >> Fax: (93) 225 99 81
> >
> >--
> >
> >Peter H. Larsen, Oracle and Applications specialist
> >Dynamic Resources Inc, Alexandria
> >Email: plarsen_at_dc.dynares.com
> >(please remove the nospam from the header when replying to this email)
> >
> >The above views are my own and does not reflect the views of my
> >employer.
>
> MarK Z, 6b, rhymingly
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Jul 15 1998 - 12:52:06 CDT