Common Mistakes 01 - rownum and order by

SQL> create table hr.numbers (id number) tablespace EXAMPLE;

Table created.

SQL> insert into hr.numbers (id) values(19);

SQL> insert into hr.numbers (id) values(3);

SQL> insert into hr.numbers (id) values(15);

SQL> insert into hr.numbers (id) values(9);

SQL> insert into hr.numbers (id) values(21);

SQL> insert into hr.numbers (id) values(7);

SQL> insert into hr.numbers (id) values(17);

SQL> insert into hr.numbers (id) values(1);

SQL> insert into hr.numbers (id) values(11);

SQL> insert into hr.numbers (id) values(5);

SQL> commit;

Commit complete.

SQL> select id from hr.numbers;

        ID

----------

        19

         3

        15

         9

        21

         7

        17

         1

        11

         5

10 rows selected.

• Common Mistake.

 A common mistake is  we want to select N minor values, we used some query like that.

SQL>  select id from hr.numbers where ROWNUM <= 5 ORDER BY id;

        ID

----------

         3

         9

        15

        19

        21

 But this query really return first found rows in ordered way.

 In order to get N minor values, we can do this.

SQL> select id from (select id from hr.numbers  ORDER BY id) where  ROWNUM <= 5;

        ID

----------

         1

         3

         5

         7

         9

Perceived 'Wrong Results' Using ROWNUM with DISTINCT or ORDER BY (Doc ID 1461173.1)

Regards,
Eini