Finding Match when using 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)

Finding Match when using Row_Number()

Author

Topic

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2012-11-14 : 12:02:03

I have this query:

with R as (
select LC.MEMBER, CT.COLLATERAL_ID, row_number() over(partition by LC.MEMBER order by CT.COLLATERAL_ID) as rn
from dbo.Table1 LC inner join dbo.Table2 as CT on LC.COLLATERAL_ID = CT.COLLATERAL_ID
)

SELECT
MEMBER
,max(case when rn = 1 then COLLATERAL_ID end) as Collateral_1
,max(case when rn = 2 then COLLATERAL_ID end) as Collateral_2
,max(case when rn = 3 then COLLATERAL_ID end) as Collateral_3
,max(case when rn = 4 then COLLATERAL_ID end) as Collateral_4
,max(case when rn = 5 then COLLATERAL_ID end) as Collateral_5
,max(case when rn = 6 then COLLATERAL_ID end) as Collateral_6
,max(case when rn = 7 then COLLATERAL_ID end) as Collateral_7
,max(case when rn = 8 then COLLATERAL_ID end) as Collateral_8
,max(case when rn = 9 then COLLATERAL_ID end) as Collateral_9
,max(case when rn = 10 then COLLATERAL_ID end) as Collateral_10
from R
group by MEMBER

So each collateral "could" have a title. I need to keep it in one row, but I am trying to do is add another column with a title number for each collateral (Null if there isn't one) from table3 where collateral_IDs match. I have tried unsuccessfully to do this and get the correct title number matches.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-14 : 12:54:33

Can't you add a JOIN to table3 in your cte definition on collateral_id returning title? Then add 10 more of these:
,max(case when rn = 1 then [Title] end) as Title_1

Be One with the Optimizer
TG

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2012-11-14 : 13:10:33

Tried this earlier:

with R as (
select LC.MEMBER, CT.COLLATERAL_ID, row_number() over(partition by LC.MEMBER order by CT.COLLATERAL_ID) as rn, LCI.TITLE
from dbo.Table1 LC inner join dbo.Table2 as CT on LC.COLLATERAL_ID = CT.COLLATERAL_ID
INNER JOIN dbo.LCollat as LCI on LCI.COLLATERAL_ID = LC.COLLATERAL_ID
)

SELECT
MEMBER
,max(case when rn = 1 then COLLATERAL_ID end) as Collateral_1
,max(case when rn = 1 then TITLE end) as TITLE_1
,max(case when rn = 2 then COLLATERAL_ID end) as Collateral_2
,max(case when rn = 1 then TITLE end) as TITLE_2...

Returns the same title for all of them

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-14 : 13:25:01

Then LCollat must have many Collateral_id values for each title?

select collateral_id, count(distinct title) from LCollat group by collateral_id having count(distinct title) > 1
and/or vice-versa
select title, count(distinct collateral_id) from LCollat group by title having count(distinct collateral_id) > 1

Be One with the Optimizer
TG

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2012-11-14 : 14:10:26

I had messed it up on my row_number and used the wrong field, when I fixed that it worked. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources