Re: simple SQL question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1999/08/07
Message-ID: <37ac44cc.1789122_at_newshost.us.oracle.com>#1/1

A copy of this was sent to "S&F" <mikan_at_corecomm.net> (if that email address didn't require changing) On Sat, 07 Aug 1999 02:13:11 GMT, you wrote:

>works for me just fine.... Wa's the prom?
>

rownum is assigned BEFORE the order by -- you do not get the data you think you will with that query. consider this example:

SQL> create table t ( x date );
Table created.

SQL> begin

  2     for i in 1 .. 5 loop
  3         insert into t values ( to_char(11-i,'fm00')||'-JAN-1999' );
  4     end loop;

  5 end;
  6 /
PL/SQL procedure successfully completed.

SQL> select rownum r, x from t;

         R X

---------- ---------
         1 10-JAN-99
         2 09-JAN-99
         3 08-JAN-99
         4 07-JAN-99
         5 06-JAN-99

SQL> select rownum r, x
  2 from t where rownum between 1 and 3   3 order by x;

         R X

---------- ---------
         3 08-JAN-99
         2 09-JAN-99
         1 10-JAN-99

SQL>
SQL> select rownum r, x
  2 from t
  3 order by x;

         R X

---------- ---------
         5 06-JAN-99
         4 07-JAN-99
         3 08-JAN-99
         2 09-JAN-99
         1 10-JAN-99

see the big difference between the two queries with the order bys. the first one pulls the first three rows and then sorts them. You get the first three rows inserted ordered (not the last three). The second one gives you the rows ordered by what you think that they really should be -- it gets all rows and then orders them.

first query : get three random rows (more or less) and sort them. second query: sort all of the data.

In Oracle8i, release 8.1, you are able to code:

SQL> select *
  2 from ( select x from t order by x )   3 where rownum <= 3
  4 /

X

---

06-JAN-99
07-JAN-99
08-JAN-99

>kev <kevin.porter_at_fast.no> wrote in article <37A84022.E177CFA1_at_fast.no>...
>> Fair comment, but my question applies to any sort of data, not just
>> temporal. Anyway, I am storing my 'time' as a Unix timestamp and I just
>> want to retrieve the rows with the top 10 (highest) values.
>>
>> I'm suspicious of rownum - any gurus tell me if this would work as I
>> described above:
>>
>> select rownum r, headline, timestamp
>> from news where rownum between 1 and 3
>> order by timestamp;
>>
>> Thanks,
>>
>> - Kev
>>
>>
>> Kenneth C Stahl wrote:
>>
>> > That would go against the concept of a relational database. If you want
>> > information like that then you would need to provide a DATE column -
>> > however, it's granularity is only to seconds and if rows are being
>> > inserted at a fast clip then it may not be as useful as you require.
>> >
>> > kev wrote:
>> >
>> > > Hi,
>> > >
>> > > Just wondering - what's the best way to retrieve, say, the latest ten
>> > > entries to a table.
>> > > Does Oracle have a MAXROWS func (I couldn't find one in the docs)
>> > >
>> > > Thanks,
>> > >
>> > > - Kev
>>
>> --
>> Kevin Porter
>> Web Programmer
>> FAST Web Media
>> Suite 320, Sunlight House, Quay St, Manchester
>> England, M3 3JZ
>>
>> Phone: 44 (0) 161 835 3525
>> Email: kevin.porter_at_fast.no
>> Home Page: http://www.fast.no
>> All The Web: http://www.alltheweb.com
>> FAST FTP Search: http://ftpsearch.lycos.com
>> FAST MP3: http://mp3.lycos.com
>> FAST Soccer: http://www.fa-premier.com
>> FAST Entertainment: http://www.innit.com
>>
>>
>>
>>

-- 
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Sat Aug 07 1999 - 00:00:00 CEST