SQL – Select with group by, Get data from the register with the max(date)

I have two tables in the database: Product and ProductVersion , each product can have n ProductVersions. ProductVersion has this fields ( Id, name, origin, date, provider )

I want a query where I get a product info and in the same register the info of the productversion with the max date, something like this:

select
p.ProductId, p.ProductName , max(pv.date), name of  max(pv.date), origin of  max(pv.date)
from Product p Join ProductVersion pv on p.ProductId = pv.ProductId
where userId = 'test_user'
group by p.Id

I want to create a view to call from c#, I am working with linq but the performace for this case is not good, so I am trying with a view with the info I need. Something like this I want to achieve with a SQL query.

var result =
_context.ProductVersion
    .Where(x => x.UserId == userId)
    .GroupBy(x => x.ProductId)
    .Select(group => group.OrderByDescending(x => x.Date).FirstOrDefault())
    .Include(x => x.Product)
    .ToList();

Answer

This should do it.

DECLARE @userID NVARCHAR(100) = N'test_user'

WITH cte
AS
(
    SELECT
        p.ProductID
        ,p.ProductName
        ,pv.Date as VersionDate
        ,pv.Name as VersionName
        ,pv.Origin as VersionOrigin
        ,ROW_NUMBER() OVER (PARTITION BY p.ProductID ORDER BY pv.Date DESC) as PartitionID
    FROM product p
        inner join ProductVersion  pv on p.ProductId = pv.ProductID
    WHERE userID = @userID
)

SELECT 
    ProductID
    ,ProductName
    ,VersionDate
    ,VersionName
    ,VersionOrigin
FROM cte
WHERE PartitionID = 1