Improve Sql Query with select max() function in where clause

Question

The purpose of this query is to bring back products and their prices for products on sale and the price should be from the date closest but not equal to the date passed in, essentially the most recent price available. There are not price records for every day. Something feels a little wrong about having the aggregate select statement in the where clause. Is there a better way to do this? Maybe in the join criteria?

        select  
        p.ProductName,
        pp.Price,
        pp.Date,
        from product p
        inner join productprice pp  on p.productid = pp.productid
        where 
        pp.evaluationdate = (select  max(Date) from productprice 
                             where productid = p.productid  
                             and date < @DateIn) 
        and p.producttype = 'OnSale'

The actually query is a little more complicated but this is essentially the problem. Thanks for your input.

EDIT There will be more than one product returned

EDIT I'm experimenting with both @Remus Rusanu's and @km's suggestions (although @Remus Rusanu removed his) all three, including my original, seem to be about the same in terms of performance. I'm trying to decide if one offers a benefit over the others in some other intangible way i.e. maintenance, self documenting etc. as this will be maintained by someone else. Thanks again.

Answer 1

SELECT TOP 1 p.ProductName, pp.Price, pp.Date,
FROM product p
INNER JOIN productprice pp on ...
WHERE pp.date < @DateIn
ORDER BY pp.date DESC

Answer 2

try this:

;WITH CurrentPrice AS 
(
SELECT productid,max(Date) AS Date
    FROM productprice 
    WHERE date < @DateIn 
    GROUP BY productid
)

select  
    p.ProductName,
    pp.Price,
    pp.Date,
    from product p
        inner join CurrentPrice pa  on p.productid = pa.productid
        inner join productprice pp  on pa.productid = pp.productid AND pa.Date=pp.Date
    where p.producttype = 'OnSale'

EDIT based on OP's comment:

I think the above query with CTE will have the same query plan as the the derived table version from @Remus Rusanu

However, if the productprice table is large, you may want to reduce it by filtering by the "OnSale" like here:

;WITH CurrentPrice AS 
(
select  
    p.productid,
    MAX(pp.Date) AS Date
    from product p
        inner join productprice pp  on pa.productid = pp.productid
    where p.producttype = 'OnSale' AND pp.date < @DateIn 
    GROUP BY productid
)
select  
    p.ProductName,
    pp.Price,
    pp.Date,
    from CurrentPrice           pa
        inner join product      p   on pa.productid = p.productid
        inner join productprice pp  on pa.productid = pp.productid AND pa.Date=pp.Date
    where p.producttype = 'OnSale'

Answer 3

Is this a job for window functions?

    SELECT * FROM (select  
            p.ProductName,
            pp.Price,
            pp.Date,
            RANK() OVER(PARTITION BY p.ProductId ORDER BY pp.Date DESC) as row_rank
            from product p
              join productprice pp  on p.productid = pp.productid
            where 
              pp.date < @DateIn
              and p.producttype = 'OnSale'
    ) saleprice
    where row_rank = 1

EDIT partitions by id (assuming your primary key is fastest), partiton on price removed