Re: a query in Oracle Report

From: David Fitzjarrell <fitzjarrell_at_cox.net>
Date: 11 Oct 2004 15:37:28 -0700
Message-ID: <9711ade0.0410111437.302b2677@posting.google.com>

Original Message ----- From: "Anthony Plunkett" <doobeh_at_thefort.org> Newsgroups: comp.databases.oracle.server Sent: Monday, October 11, 2004 3:27 PM Subject: Re: a query in Oracle Report

> mo wrote:

>> when I issue a query in Oracle Report:
>>
>> 1. select * from
>> 2. (
>> 3. select first_name, last_name
>> 4. from students
>> 5. order by first_name
>> 6. )
>> 7. where rownum<3;
>>
>> I received a warning of missing a ")", if I
>> omit the 5th line, it's fine. should I use a cursor
>> to solve it?

> 
> Why not just use:
> 
> SELECT first_name, last_name
> FROM students
> WHERE rownum <3
> ORDER BY first_name;
> 
> Saves on a subquery.. ?
> 
> Anthony.

Because it doesn't. The original query orders the results THEN assigns ROWNUM to the ordered result set. Yours does no such thing; thus yours returns an erroneous result. To illustrate the point:

SQL> create table students(first_name varchar2(20), last_name varchar2(20), studentid number);

Table created.

SQL> insert into students values ('Pillsbury','Doughboy',3502375);

1 row created.

SQL> insert into students values ('David','Darwin',999223478);

1 row created.

SQL> insert into students values ('Raul','Ravioli',777777777);

1 row created.

SQL> insert into students values('Almonso','Wannamacher',123456789);

1 row created.

SQL> insert into students values ('Edgar','Quonset',876543321);

1 row created.

SQL> insert into students values ('Morfeo','Quesoverde', 333445555);

1 row created.

SQL> commit;

Commit complete.

SQL> select first_name, last_name
  2 from students
  3 where rownum < 3
  4 order by first_name;

FIRST_NAME LAST_NAME

-------------------- --------------------
David                Darwin
Pillsbury            Doughboy

SQL> select * from
  2 (select first_name, last_Name
  3 from students
  4 order by first_name)
  5 where rownum < 3;

FIRST_NAME LAST_NAME

-------------------- --------------------
Almonso              Wannamacher
David                Darwin

SQL> The results are not the same, nor should they be.

David Fitzjarrell Received on Mon Oct 11 2004 - 17:37:28 CDT