This discussion is archived
11 Replies Latest reply: Feb 18, 2013 4:26 AM by Sen2008 RSS

ROWNUM in UNION operator

Sen2008 Explorer
Currently Being Moderated
All,

I have a requirement in SQL that I have to number each row. Hence I thought of using ROWNUM. But the sql query I'm using uses UNION operator. Hence I used like this

select a,b,rownum as 'field1' from table1
union
select c,d,1 as 'field1' from table2

Will the above query solve my purpose?

thanks
sen
  • 1. Re: ROWNUM in UNION operator
    jeneesh Guru
    Currently Being Moderated
    You could use sub query..
    select a,b,rownum rn
    from
    (
    select a,b, from table1
    union
    select c,d from table2
    );
    between,there is a difference between UNION and UNION ALL. Are you intentionally using UNION ?

    Edited by: jeneesh on Feb 18, 2013 4:02 PM
  • 2. Re: ROWNUM in UNION operator
    Purvesh K Guru
    Currently Being Moderated
    Use a ROW_NUMBER analytic function. I find it more reliable than ROWNUM.

    select a,b,row_number() over (order by 1) as 'field1' from table1
    union
    select c,d,1 as 'field1' from table2
  • 3. Re: ROWNUM in UNION operator
    Sen2008 Explorer
    Currently Being Moderated
    thanks jeneesh, i'll try out and update.

    thanks for the quick response

    sen
  • 4. Re: ROWNUM in UNION operator
    793996 Explorer
    Currently Being Moderated
    Try this.

    select a,b, field1 from (select a,b,rownum field1 from table1) t1
    union
    select c,d,1 field1 from table2

    If it doesnt solve your purpose kindly provide some sample data and output.

    Thanks,
    Vivek
  • 5. Re: ROWNUM in UNION operator
    jeneesh Guru
    Currently Being Moderated
    Purvesh K wrote:
    Use a ROW_NUMBER analytic function. I find it more reliable than ROWNUM.

    select a,b,row_number() over (order by 1) as 'field1' from table1
    union
    select c,d,1 as 'field1' from table2
    This will probably kill the purpose of UNION - which is to take only the DISTINCT rows..

    If DISTINCT is not intended, it is better to choose UNION ALL
  • 6. Re: ROWNUM in UNION operator
    Sen2008 Explorer
    Currently Being Moderated
    Do you guys see any issue with my query? Will this solve the pbm?

    select a,b,rownum as 'field1' from table1
    union
    select c,d,1 as 'field1' from table2


    thanks
    sen
  • 7. Re: ROWNUM in UNION operator
    jeneesh Guru
    Currently Being Moderated
    Sen2008 wrote:
    Do you guys see any issue with my query? Will this solve the pbm?

    select a,b,rownum as 'field1' from table1
    union
    select c,d,1 as 'field1' from table2


    thanks
    sen
    As already mentioned there is a difference between UNION and UNION ALL.

    UNION will result in a unique set of rows..

    If you don't expect duplicate values, you dont need to use UNION - it is costly compared to UNION ALL.

    And in your query you may get "1" (In field1) two times..
  • 8. Re: ROWNUM in UNION operator
    Sen2008 Explorer
    Currently Being Moderated
    jeneesh,

    I tried ur query like below

    select a,b,rownum rn
    from
    (
    select a,b, from table1
    union
    select c,d from table2
    );

    But got ora-00904 - (ex: 'a' invalid identifier)!

    rgds
    sen
  • 9. Re: ROWNUM in UNION operator
    jeneesh Guru
    Currently Being Moderated
    Sen2008 wrote:
    jeneesh,

    I tried ur query like below

    select a,b,rownum rn
    from
    (
    select a,b, from table1
    union
    select c,d from table2
    );

    But got ora-00904 - (ex: 'a' invalid identifier)!

    rgds
    sen
    post the result of - DESC table1

    between there is an extra comma in the UNION query
    select a,b,rownum rn
    from
    (
    select a,b from table1
    union
    select c,d from table2
    );
  • 10. Re: ROWNUM in UNION operator
    Sen2008 Explorer
    Currently Being Moderated
    I just copied your query as it is, but in the actual query I did it right i.e no extra comma in my query
  • 11. Re: ROWNUM in UNION operator
    Purvesh K Guru
    Currently Being Moderated
    jeneesh wrote:
    This will probably kill the purpose of UNION - which is to take only the DISTINCT rows..

    If DISTINCT is not intended, it is better to choose UNION ALL
    Yes, you are correct about use of UNION. Probably, OP would have wanted to use Union ALL perhaps, but might have forgotten about suppressing of duplicates and sorting and ended up using Union.

    But, in accordance with the original query used by OP, my suggestion of row number would not affect the output, I guess, would it?

    Perhaps, if OP would have provided us with some base sample data and the Output desired from it, it would have been of greater help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points