I need some help with a query. I have written a query that returns the data as anticipated. However, now I need to modify it to provide only those records with the largest PID.INTERVAL_NUMBER when grouped by the PID.POLICY_ID. For example, my query returns policy ID POL45379 three times, with interval numbers 1, 2, and 3. I would like to only show the data associated with PID.INTERVAL_NUMBER 3. I have reviewed several threads, but my attempts were fruitless. Any help would be greatly appreciated.
Here is the current code:
FROM dbo.PR_POLICY_DESCRIPTION PD
INNER JOIN PR_ITEM_DESCRIPTION PID
WHERE ((PID.EFFECTIVE_DATE BETWEEN '2014-01-01' AND '2014-03-31') OR
((PID.EFFECTIVE_DATE < '2014-01-01') AND (PID.EXPIRATION_DATE BETWEEN '2014-01-01' AND '2014-03-31')) OR
((PID.EFFECTIVE_DATE < '2014-01-01') AND (PID.EXPIRATION_DATE >= '2014-03-31')))
ORDER BY PID.POLICY_ID, PID.ITEM_NUMBER, PID.INTERVAL_NUMBER