SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 capture result of row_number()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

447 Posts

Posted - 04/07/2014 :  03:04:14  Show Profile  Reply with Quote
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

447 Posts

Posted - 04/07/2014 :  03:25:00  Show Profile  Reply with Quote
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'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000