SQL Query, SELECT Top 2 by Foreign Key Order By Date

Question

I need a SQL query that returns the top 2 Plans by PlanDate per ClientID. This is all on one table where PlanID is the PrimaryID, ClientID is a foreignID.

This is what I have so far -->

SELECT *
FROM [dbo].[tblPlan] 
WHERE [PlanID] IN (SELECT TOP (2) PlanID FROM [dbo].[tblPlan] ORDER BY [PlanDate] DESC)

This, obviously, only returns 2 records where I actually need up to 2 records per ClientID.

Answer 1

Edit: The corss apply solution only works with a the client table or a distinct clientid list as the alias p1. So the cross apply is per client. My solution here is per row in plan where clientid is repeated. Oops.

SELECT *
FROM
  [dbo].[tblPlan] P1
  CROSS APPLY
  (SELECT TOP (2) P2.PlanID
   FROM [dbo].[tblPlan] P2
   WHERE P1.ClientID = P2.ClientID
   ORDER BY P2.[PlanDate] DESC) foo

You should use this

;WITH cTE AS (
  SELECT
     *,
     ROW_NUMBER () OVER (PARTITION BY clientid ORDER BY [PlanDate] DESC) AS Ranking
  FROM
     [dbo].[tblPlan]
)
SELECT * FROM cTE WHERE Ranking <= 2

Answer 2

This can be done using ROW_NUMBER:

SELECT PlanId, ClientId, PlanDate FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY ClientId ORDER BY PlanDate DESC) rn, *
    FROM [dbo].[tblPlan]
) AS T1
WHERE rn <=2

Add any other columns you need to the select to get those too.