ROWNUM and ORDER BY

Just a quick revisit on an old topic so I could link to this post in a Stack Overflow discussion.



SQL> create table t ( x int );

Table created.

SQL>
SQL> insert into t
  2  select dbms_random.value(1,1000)
  3  from dual
  4  connect by level <= 100;

100 rows created.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
       100

1 row selected.

--
-- DOES NOT WORK
--
SQL>
SQL> select * from t
  2  where rownum <= 10
  3  order by x desc;

         X
----------
       995
       909
       873
       871
       640
       555
       318
       197
       134
        70

10 rows selected.

--
-- CORRECT ALTERNATIVES
--

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

         X
----------
      1000
       995
       991
       977
       977
       971
       955
       909
       909
       900

10 rows selected.

SQL> select * from t
  2  order by x desc
  3  fetch first 10 rows only;

         X
----------
      1000
       995
       991
       977
       977
       971
       955
       909
       909
       900

10 rows selected.

SQL>
SQL> select x
  2  from
  3    ( select t.*,
  4         row_number() over ( order by x desc ) r
  5      from t  )
  6  where r <= 10;

         X
----------
      1000
       995
       991
       977
       977
       971
       955
       909
       909
       900

10 rows selected.

As you can see, you cannot simply have WHERE ROWNUM and ORDER BY in the same SELECT statement if you are after the “top” rows based on some criteria.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.