11 Replies Latest reply: Feb 18, 2013 6:26 AM by Sen2008 RSS

    ROWNUM in UNION operator

    Sen2008
      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
          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
            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
              thanks jeneesh, i'll try out and update.

              thanks for the quick response

              sen
              • 4. Re: ROWNUM in UNION operator
                793996
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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.