Running number on Oracle UNION

Question

What I need is a union query with running number as one of column selected. I did try including ROWNUM as part of the query but it gets duplicated over each query. ROWNUM is being reset.

What is the best way to achieve this?

edit:Oracle 9i.

Answer 1

Try something like this:

SELECT COL1,
       COL2,
       COL4,
       ROW_NUMBER() OVER (ORDER BY SORT_COLUMN ) "ROW_NUMBER"
FROM   (
     SELECT COL1,
            COL2,
            COL4,
            SORT_COLUMN
     FROM TABLE_A
     UNION ALL
     SELECT COL1,
            COL2,
            COL4,
            SORT_COLUMN
     FROM TABLE_B
  )