Oracle: Display row number with 'order by' clause

Question

Hello. I wonder how could i print a row number for sql statement where is using order. Currently i tried ROWNUM but as i understand it works only for unsorted result set.

SELECT rownum, a.lg_id, a.full_name, a.sort_order
  FROM activity_type_lang a
  where a.lg_id = 'en'
  order by a.full_name;

TIA

Answer 1

Oh. Seems i've found already a solution.

    Select rownum, lg_id, full_name, sort_order from 
(SELECT a.lg_id, a.full_name, a.sort_order
      FROM activity_type_lang a
      where a.lg_id = 'en'
      order by a.full_name);

Answer 2

rownum is applied before ordering, so you have to rewrite your query like this: select rownum, xxx.* from ( SELECT a.lg_id, a.full_name, a.sort_order FROM activity_type_lang a where a.lg_id = 'en' order by a.full_name ) xxx;

Answer 3

In addition to nesting the query, you can use an analytic function

SELECT row_number() OVER (ORDER BY a.full_name),
       lg_id,
       full_name,
       sort_order
  FROM activity_type_lang a
 WHERE a.lg_id = 'en'
 ORDER BY a.full_name

Using analytic functions also makes it easier if you want to change how ties are handled. You can replace ROW_NUMBER with RANK or DENSE_RANK.

Answer 4

Hello. I wonder how could i print a row number for sql statement where is using order. Currently i tried ROWNUM but as i understand it works only for unsorted result set.

To be clear (somebody might get it wrong). It does work (but not in the way you expect). The problem with it is that it "attaches" the ROWNUM before the sort and you get your records but not in consecutive ROWNUM records. Why? Because

The first record that meets the where criteria in a select statement is given rownum=1

This is really good example how select / order mechanism works.