CODEWITHSUNDEEP
sql-server
Jim400
Jim400

Reputation: 25

Rows with highest values from second and third tables

I have three linked tables in SQL Server (Customers, Policies and Claims), and need to get a record for each Customer showing their most recent Policy, and within that Policy, their most recent Claim. The highest PolicyID for the Customer is their most recent Policy, and the highest ClaimID is the most recent Claim for the Policy.

I have the following query to show all Policies/Claims, but how do I restrict them to the most recent/highest IDs?

SELECT C.CustomerID, C.FirstName, C.LastName, P.PolicyID, P.PolicyDate, P.PolicyType, CL.ClaimID, CL.ClaimDate, CL.ClaimDescription
FROM Customers C INNER JOIN Policies P ON C.CustomerID = P.CustomerID
INNER JOIN Claims CL ON P.PolicyID = CL.PolicyID

Upvotes: 2

Views: 32

Answers (1)

Jatin Patel
Jatin Patel

Reputation: 2104

try this,

;WITH CTE AS
(
    SELECT C.CustomerID, C.FirstName, C.LastName, P.PolicyID, P.PolicyDate, P.PolicyType, CL.ClaimID, CL.ClaimDate, CL.ClaimDescription
        ,ROW_NUMBER() OVER(PARTITION BY C.CustomerID ORDER BY P.PolicyID DESC) PolicyOrder
        ,ROW_NUMBER() OVER(PARTITION BY C.CustomerID, P.PolicyID ORDER BY CL.ClaimID DESC) ClaimOrder
    FROM Customers C INNER JOIN Policies P ON C.CustomerID = P.CustomerID
    INNER JOIN Claims CL ON P.PolicyID = CL.PolicyID
)
SELECT 
    *
FROM CTE c
WHERE c.PolicyOrder = 1
    AND c.ClaimOrder = 1

Upvotes: 1

Related Questions