ROW_NUMBER and UNION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ROW_NUMBER and UNION

Author

Topic

almir
Starting Member

10 Posts

Posted - 2007-10-06 : 10:28:09

Hi
I have a pretty complex select statement using UNION to pull data from 2 tables.

Something like this:

(Select field1,field2 from t1 where field1 = @param1) UNION
(Select field1,field2 from t2 where field2 = @param1) Order by field1

It is much more complex than this but you get the picture. I would like to add unique identifier for each row with Row_Number function based on ordering field similar to the line bellow:

ROW_NUMBER() OVER(Order by field1 ASC) as rowid,

If I add them within respective select statements they will all generate their own sequence resulting in rowid duplicate values.

Is it possible to generate unique rowid for each row?

Your help will be greatly appreciated

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-06 : 10:31:58

select ROW_NUMBER() OVER(Order by field1 ASC) as rowid, <otherColumns>
from (
(Select field1,field2 from t1 where field1 = @param1)
UNION
(Select field1,field2 from t2 where field2 = @param1)
) t1
Order by field1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

almir
Starting Member

10 Posts

Posted - 2007-10-06 : 11:49:06

Thanx spirit

You put me on the right track, I was playing with subqueries but since the statement is dynamically constructed I had an error that was difficult to debug. Everything works ok now. The purpose of whole exercise is paging of the result set.

I'll be passing soon through Slovenia on my way to UK Ptuj - Maribor - Ostereich grenz

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-06 : 12:41:48

car or train?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

almir
Starting Member

10 Posts

Posted - 2007-10-06 : 12:48:51

car

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-06 : 12:53:06

too bad i live in the capital which is a bit way off... we could go grab a beer

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

almir
Starting Member

10 Posts

Posted - 2007-10-06 : 12:59:55

Since I don't drink I would have a coke, but you never know I might make detour through Northern Italy and France to show my family different side of Europe :-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-06 : 13:13:10

coke's ok too

well if you decide to go through ljubljana drop me mail.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-08 : 04:24:26

Is L silent in ljubljana?

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-08 : 04:30:25

nope.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-08 : 06:18:41

Then I wonder how I can pronounce it

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-08 : 06:52:43

i don't think you can correctly

the closest english speakers can get is:
lube-ya-na
look here:
http://query.nytimes.com/gst/fullpage.html?sec=travel&res=990DE3D91731F935A1575BC0A9679C8B63

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

AJA
Starting Member

3 Posts

Posted - 2009-08-18 : 08:32:42

Hi spirit1,

I am wandering for a better option, don't you think using a subquery will degrade the performance.

I have to again select the data from the resultset for paging using the Row_Number() I have generated, but it costs much because first I have a Union then I also have subquery and finally i am returning the result.

Please suggest if i can do better in case.

Thanks in advance.

I wish, I wasn't so nice !

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-08-18 : 08:35:25

Please start a new thread for your question.
in there post your example code.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

AJA
Starting Member

3 Posts

Posted - 2009-08-18 : 09:04:58

Sprint1, I have started new thread "Row_Number and union".

please suggest.

I wish, I wasn't so nice !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources