How to select from a table without including repeated column values?
SQLfiddle

SELECT H.*
FROM History AS H
OUTER APPLY
(
    SELECT TOP (1)
        H2.Price
    FROM History AS H2
    WHERE
        H2.Product = H.Product
        AND H2.Kind = H.Kind
        AND H2.Effective &lt; H.Effective
    ORDER BY
        H2.Effective DESC
) AS X
WHERE
    NOT EXISTS (SELECT X.Price INTERSECT SELECT H.Price);




This is a good execution plan for the small number of rows given in the question. For a larger table, the ideal index for this query is:

CREATE UNIQUE INDEX [dbo.History Product, Kind, Effective]
ON dbo.History (Product, Kind, Effective DESC)
INCLUDE (Price);




That index is essentially the clustered index keys in a more helpful order. Depending on how the table is used for other queries, it might be better to replace the clustered index instead of creating this new index.

Do not use batch estimated cost percentages to compare different queries. This is not a valid comparison in general. The costs are always optimizer estimates, and not intended to be used this way. Check actual performance metrics (elapsed time, I/Os, CPU usage, memory usage) by all means, but do not put your faith in the percentages.

Whether this query or the one based on ROW_NUMBER is more efficient depends on the distribution of the data, and other factors. Each has their advantages and disadvantages. This query has advantages when there are many rows for each (product, kind) combination.