Select distinct values in column with condition
Solution 1

The idea is to find distinct groups for each product, then the highest priority match calculated using a CASE expression:

SELECT
    P.ProductId,
    P.ItemId,
    G.[Group],
    P.Color,
    V.[Value]
FROM dbo.Products AS P
CROSS APPLY 
(
    -- Find groups for the current ItemId
    SELECT DISTINCT
        PP.[Group]
    FROM dbo.ProductPrices AS PP
    WHERE
        PP.ItemId = P.ItemId
) AS G
CROSS APPLY 
(
    -- Find the highest-priority Value
    SELECT TOP (1)
        PP2.[Value]
    FROM dbo.ProductPrices AS PP2
    WHERE
        PP2.ItemId = P.ItemId
        AND PP2.[Group] = G.[Group]
    ORDER BY
        CASE
            -- Colour match is best
            WHEN PP2.Color = P.Color THEN 1
            -- Otherwise accept NULL
            WHEN PP2.Color IS NULL THEN 2
            ELSE 3
        END ASC
) AS V
ORDER BY 
    P.ProductId,
    P.ItemId,
    G.[Group] DESC;


Solution 2

This joins the tables in the natural way, then uses a window function to find the highest priority match for each group. This code only accesses each base table once:

WITH
    Joined AS
    (
        SELECT
            P.ProductId,
            P.ItemId,
            PP.[Group],
            Color = ISNULL(PP.Color, P.Color),
            PP.[Value],
            rnk = 
                CASE
                    WHEN PP.Color = P.Color THEN 1
                    WHEN PP.Color IS NULL THEN 2
                    ELSE 3
                END
        FROM dbo.Products AS P
        JOIN dbo.ProductPrices AS PP
            ON PP.ItemId = P.ItemId
    ),
    Ranked AS
    (
        SELECT
            *,
            rn = 
                ROW_NUMBER() OVER (
                    PARTITION BY Joined.ItemId, Joined.[Group], Joined.Color
                    ORDER BY Joined.rnk ASC)
        FROM Joined
    )
SELECT
    R.ProductId,
    R.ItemId,
    R.[Group],
    R.Color,
    R.[Value]
FROM Ranked AS R
WHERE 
    R.rn = 1
ORDER BY 
    R.ProductId,
    R.ItemId,
    R.[Group] DESC;


Solution 3

This is the same logic as solution 2, but uses a subquery and a more compact syntax:

SELECT
    Q1.ProductId,
    Q1.ItemId,
    Q1.[Group],
    Q1.Color,
    Q1.[Value]
FROM 
(
    SELECT
        P.ProductId,
        P.ItemId,
        PP.[Group],
        Color = ISNULL(PP.Color, P.Color),
        PP.Value,
        rn = ROW_NUMBER() OVER (
            PARTITION BY P.ItemId, PP.[Group], ISNULL(PP.Color, P.Color)
            ORDER BY
                CASE
                    WHEN PP.Color = P.Color THEN 1
                    WHEN PP.Color IS NULL THEN 2
                    ELSE 3
                END)
    FROM dbo.Products AS P
    JOIN dbo.ProductPrices AS PP
        ON PP.ItemId = P.ItemId
) AS Q1
WHERE
    Q1.rn = 1
ORDER BY
    Q1.ProductId,
    Q1.ItemId,
    Q1.[Group] DESC;


Solution 4

With the following index on dbo.ProductPrices:

CREATE INDEX [IX dbo.ProductPrices ItemId, Group (Color)]
ON dbo.ProductPrices
(
    ItemId,
    [Group],
    Color
)
INCLUDE
(
    [Value]
);


The following solution can perform well:

SELECT
    P.ProductId,
    P.ItemId,
    G.[Group],
    P.Color,
    [Value] =
        IIF
        (
            EXISTS
            (
                SELECT
                    1
                FROM dbo.ProductPrices AS PP
                WHERE
                    PP.ItemId = CONVERT(varchar(50), P.ItemId)
                    AND PP.[Group] = G.[Group]
                    AND PP.Color = CONVERT(varchar(50), P.Color)
            ),
            (
                SELECT TOP (1)
                    PP.[Value]
                FROM dbo.ProductPrices AS PP
                WHERE
                    PP.ItemId = CONVERT(varchar(50), P.ItemId)
                    AND PP.[Group] = G.[Group]
                    AND PP.Color = CONVERT(varchar(50), P.Color)
            ),
            (
                SELECT TOP (1)
                    PP.[Value]
                FROM dbo.ProductPrices AS PP
                WHERE
                    PP.ItemId = CONVERT(varchar(50), P.ItemId)
                    AND PP.[Group] = G.[Group]
                    AND PP.Color IS NULL
            )
        )
FROM dbo.Products AS P
CROSS APPLY 
(
    SELECT DISTINCT TOP (9223372036854775807)
        PP.ItemId,
        PP.[Group]
    FROM dbo.ProductPrices AS PP
    WHERE
        PP.ItemId = CONVERT(varchar(50), P.ItemId)
) AS G
ORDER BY 
    P.ProductId,
    P.ItemId,
    G.[Group] DESC;


Demos

db&lt;>fiddle demo

As a side note, ItemId is defined as nvarchar(50) in one table, and varchar(50) in the other. Same with the Color column. These problems should be fixed, if possible.