self join with rank

[FALCONSEYE]

I have the following table:

USE xxx
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[branches](
	[branchNumber] [int] NULL,
	[isSub] [char](1) NULL,
	[masterBranch] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

insert into branches 
values ( 115, 'Y', 900 ) 


insert into branches 
values ( 123, 'Y', 900 ) 

insert into branches 
values ( 150, 'Y', 900 ) 

insert into branches 
values ( 900, 'N', null ) 

insert into branches 
values ( 901, 'N', null ) 
go

I need to check
a) Is 115 a isSub = Y? If it is, then I am done.
b) Is 900 a IsSub = Y? If not, I need to return : 115, 123, 150, 900.
c) is 901 a isSub = Y? If not, I need to return 901.

It has been quite sometime since I looked at rank() function so I am kind of stuck at the moment. Any help will be appreciated.
thanks


ColdFusion Ninja for hire.

 

[FALCONSEYE]

i am sure there is a better solution but this one works for now...

select distinct branchNumber as num from dbo.branches
where branchNumber = 115
union
select branchNumber as num from dbo.branches
where masterBranch = 115

select distinct branchNumber as num from dbo.branches
where branchNumber = 900
union
select branchNumber as num from dbo.branches
where masterBranch = 900

ColdFusion Ninja for hire.