Oracle: Display row number with 'order by' clause

Question

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

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.