11 Replies Latest reply on Feb 18, 2013 11:20 AM by Purvesh K

# ROWNUM in UNION operator

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

thanks for the quick response

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