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

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Problem

Re: SQL Problem

From: <Markz_at_starnet.lenfest.com>
Date: Wed, 15 Jul 1998 13:18:33 GMT
Message-ID: <35acaaf0.153601547@news1.fast.net>


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 Received on Wed Jul 15 1998 - 08:18:33 CDT

Original text of this message

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