xQuery - Returning MAX Row

Question

I'm pulling what hair I have left out! The resultset is all good but I now need to do something else. Here's the T-SQL

SELECT  documentdate
,       x.a.value('(Date)[1]','varchar(50)') as [Date]
,       x.a.value('(ReadCode)[1]','varchar(50)') as [Read Code]
,       x.a.value('(Rubric)[1]','varchar(200)') as [Rubric]
,       x.a.value('(Notes)[1]','varchar(200)') as [Notes]
,       x.a.value('(Notes1)[1]','varchar(200)') as [Notes1]
,       x.a.value('(Episodicity)[1]','varchar(50)') as [Episodicity]
,       REF.dbo.PATs.OptedOut

FROM
EPR.dbo.PCTX_CONT t INNER JOIN
REF.dbo.PATNumbers 
ON t.PATNoID = REF.dbo.PATNumbers.PATNoID

INNER JOIN
REF.dbo.PATs ON 
REF.dbo.PATNumbers.CurrentNo = 
REF.dbo.PATs.ManorNo

CROSS APPLY
t.DocumentXML.nodes('//CONT') x(a)

WHERE REPLACE(REF.dbo.PATNumbers.CurrentNo,' ','')=123456789

The results are

2010-05-13  8I64.
2010-05-13  8I6C.
2010-02-09  8I24.
2010-02-09  8I65.
2010-02-09  8I26.
2010-02-09  8I6B.
2009-06-02  8I24.
2009-03-17  8I26.
2009-01-06  8I64.
2009-01-06  8I6C.
2006-11-14  8I74.
2006-11-14  8I75.

However what I need is the latest row for a code so the results would read

2010-05-13  8I64.
2010-05-13  8I6C.
2010-02-09  8I24.
2010-02-09  8I65.
2010-02-09  8I26.
2010-02-09  8I6B.
2006-11-14  8I74.
2006-11-14  8I75.

What are the names of the two columns in the output? – Andriy M Sep 01 '11 at 10:47 — Andriy M, Sep 01 '11 at 10:47

t-clausen.dk · Accepted Answer · 2011-09-01T14:23:36.300

I am guessing that your codes are the Rubric column

select documentdate,[Date], [Read Code], [Rubric], [Notes], [Notes1],
[Episodicity],REF.dbo.PATs.OptedOut FROM 
(
SELECT  documentdate  
,       x.a.value('(Date)[1]','varchar(50)') as [Date]  
,       x.a.value('(ReadCode)[1]','varchar(50)') as [Read Code]  
,       x.a.value('(Rubric)[1]','varchar(200)') as [Rubric]  
,       x.a.value('(Notes)[1]','varchar(200)') as [Notes]  
,       x.a.value('(Notes1)[1]','varchar(200)') as [Notes1]  
,       x.a.value('(Episodicity)[1]','varchar(50)') as [Episodicity]  
,       REF.dbo.PATs.OptedOut  
,       row_number() over (partition by x.a.value('(Rubric)[1]','varchar(200)') 
        order by x.a.value('(Date)[1]','varchar(50)') desc) as rn
FROM  
EPR.dbo.PCTX_CONT t INNER JOIN  
REF.dbo.PATNumbers   
ON t.PATNoID = REF.dbo.PATNumbers.PATNoID  

INNER JOIN  
REF.dbo.PATs ON   
REF.dbo.PATNumbers.CurrentNo =   
REF.dbo.PATs.ManorNo  

CROSS APPLY  
t.DocumentXML.nodes('//CONT') x(a)  

WHERE REPLACE(REF.dbo.PATNumbers.CurrentNo,' ','')=123456789
) a where rn = 1

+1, but the last column in the outer SELECT should probably be simply `OptedOut`, without all those qualifiers (because the outer SELECT knows nothing about the `REF.dbo.PATs` table). — Andriy M, Sep 02 '11 at 12:29
@AndriyM This is just between you and me, don't tell anyone else. I recycled most of the syntax, only changed the nessasary part. I have been unable to test the whole, I tested the part I changed on my own tables and replaced it in the existing query. — t-clausen.dk, Sep 02 '11 at 12:54

xQuery - Returning MAX Row

1 Answers1