CODEWITHSUNDEEP
sqlsql-server-2012
iDesi
iDesi

Reputation: 712

Sql server - Get last occurrence of an XML attribute

I have the following data in a table enter image description here

And I'm trying to write a query which will return the row which contains the last occurrence of a given ID. For example :- In the case of the example above, it'll return two rows (ID#282 & ID#266)

How will I go about writing this?

Upvotes: 0

Views: 147

Answers (1)

Martin Smith
Martin Smith

Reputation: 453298

WITH T1
     AS (SELECT *,
                XML.value(( '(//Customer/@ID)[1]' ), 'int') AS CustomerID
         FROM   YourTable),
     T2
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Id DESC) AS RN
         FROM   T1)
SELECT Id,
       CustomerID,
       XML
FROM   T2
WHERE  RN = 1

Upvotes: 1

Related Questions