capture result of 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 2008 Forums

Transact-SQL (2008)

capture result of row_number()

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-04-07 : 03:04:14

Hi Expert,

I have a query to filter all acode is equal to "IPH" under acode column but i wanted to display the description of where the RN is equal to 2. May I ask your help guys on how to make this in t-SQL. thanks.

btw, the RN column is the result if ROW_NUMBER() over by particion.

here is the sample data:

Drop table #sample

Create Table #sample
(NAME nvarchar(35), trndate datetime, category  nvarchar(35), RN int, wrkctr nvarchar(35), acode nvarchar(5), descript nvarchar(35))
Insert into #sample(name,trndate, category, RN, wrkctr, acode,descript) values('xxxx','2014-04-01','fail',1,'','IPH','')
Insert into #sample(name,trndate, category, RN, wrkctr, acode,descript) values('xxxx','2014-04-01','fail',2,'552','SELL','Defect')

Insert into #sample(name,trndate, category, RN, wrkctr, acode,descript) values('bbbb','2014-04-02','fail',1,'','IPH','')
Insert into #sample(name,trndate, category, RN, wrkctr, acode,descript) values('bbbb','2014-04-02','fail',2,'553','SELL','unacceptable')

Insert into #sample(name,trndate, category, RN, wrkctr, acode,descript) values('ssss','2014-04-03','fail',1,'','IPH','')
Insert into #sample(name,trndate, category, RN, wrkctr, acode,descript) values('ssss','2014-04-03','fail',2,'','MAN','')



select 
   NAME, trndate, category, RN, wrkctr , acode, descript
from #sample
where acode='IPH'

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-04-07 : 03:25:00

I come up with my solution. any advice. thanks.

select 
   t.NAME, t.trndate, t.category, t.trndate, t.wrkctr , t.acode, x.descript
from #sample t
Left Outer Join (Select name, descript, wrkctr  from #sample where 
    RN=2) x
    On x.name=t.name
where acode='IPH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources