Joining a view containing Row_Number()

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)

Joining a view containing Row_Number()

Author

Topic

WaterWolf
Starting Member

24 Posts

Posted - 2009-10-14 : 09:47:24

I have a view which selects from a table that applies an integer value to each row using row_number():

SELECT ID, Name, Row_Number() OVER (ORDER BY (SELECT 1)) AS EmployeeNumber
FROM tableA

Which returns something like

ASD, Fred, 1
ASB, Sam, 2
ATD, Fred, 3
FSD, Fred, 4

Which is fine and as I want it.

I am joining another view using the ID column. There can be multiple records with the same ID. The problem is that when joined, the row number changes rather than keeping its value from the first view. Eg:

Sales, ASD, Fred, 1
Marketing, ASD, Fred, 2
Sales, ASB, Sam, 3
IT, ATD, Fred, 4
IT, FSD, Fred, 5

when what I really wanted was:

Sales, ASD, Fred, 1
Marketing, ASD, Fred, 1
Sales, ASB, Sam, 2
IT, ATD, Fred, 3
IT, FSD, Fred, 4

Is there a way I can join these views while retaining the row_number() from the first view? As I'm using views here I can't use an auto number.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-14 : 09:50:35

select required_columns from your_view as v inner join
(
SELECT ID, Name, Row_Number() OVER (ORDER BY (SELECT 1)) AS EmployeeNumber
FROM tableA
) as t
on v.key_col=t.key_col

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources