Can I get the Last Date a product had its Cost changed and that relevant cost from a union table I have created?

I wrote a query to extract the most recent price of products and what that price was from tables in our company's SQL Server database and used UNION to join it with the most recent delivery price for those same products. Is there a way I can now nest that union query into another one to find the newest price from the unionised table?

This is the code of the working union query:

select  LastCostDate,
        [LocStock].SiteID,      
        [LastDate].PLUID,
        [LastDate].Description,
        [LastCost].Cost
From    (select max([PLUCostHistory].ChangeDate) as LastCostDate, [PLUCostHistory].PLUID, [PLU].Description From [PLUCostHistory] Inner Join [PLU]
        on [PLU].PLUID=[PLUCostHistory].PLUID group by [PLUCostHistory].PLUID, [PLU].Description ) as LastDate

Inner Join (select [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description, sum([PLUCostHistory].Cost) as Cost From [PLUCostHistory] Inner Join [PLU]
        on [PLU].PLUID=[PLUCostHistory].PLUID group by [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description ) as LastCost
On      LastDate.Description=LastCost.Description
And     LastDate.LastCostDate=LastCost.ChangeDate
And     LastDate.PLUID=LastCost.PLUID
Inner Join [LocStock]
        on [LocStock].PLUID=[LastDate].PLUID

UNION

select  LastCostDate,
        [DelMast].SiteID,       
        [LastDate].PLUID,
        [LastDate].Description,
        [LastCost].Cost
From    (select max([DelMast].DeliveryDate) as LastCostDate, [DelDets].PLUID, [PLU].Description From [DelMast] Inner Join [DelDets]
        on [DelDets].DeliveryID=[DelMast].DeliveryID Inner Join [PLU] on [PLU].PLUID=[DelDets].PLUID Inner Join [PLUGroupRef]
        on [DelDets].PLUID=[PLUGroupRef].PLUID Inner Join [PLUGroup2]
        on [PLUGroup2].PLUGroup2ID=[PLUGroupRef].PLUGroup2ID group by [DelDets].PLUID, [PLU].Description) as LastDate

Inner Join (select [DelMast].DeliveryDate, [DelDets].PLUID, [PLU].Description, sum([DelDets].Cost) as Cost From [DelMast] Inner Join [DelDets] on [DelDets].DeliveryID=[DelMast].DeliveryID Inner Join [PLU]
        on [PLU].PLUID=[DelDets].PLUID group by [DelMast].DeliveryDate, [DelDets].PLUID, [PLU].Description) as LastCost
On      LastDate.Description=LastCost.Description
And     LastDate.LastCostDate=LastCost.DeliveryDate
And     LastDate.PLUID=LastCost.PLUID
Inner Join [LocStock]
        on [LocStock].PLUID=[LastDate].PLUID
Inner Join [DelMast] 
        on [LocStock].SiteID=[DelMast].SiteID

Table 1

ChangeDate	Product	Cost
01/01/2000	Water	5
06/01/2000	Banana	2
09/01/2000	orange	3
10/01/2000	Water	3
01/01/2000	Banana	2.5

Table 2

ChangeDate	Product	Cost
08/01/2000	Water	6
03/01/2000	Banana	1
05/01/2000	Water	3
02/01/2000	Banana	3
12/01/2000	orange	4

Desired output:

ChangeDate	Product	Cost
10/01/2000	Water	3
06/01/2000	Banana	2
12/01/2000	orange	4

Solution

Nevermind my comment above. I came up with the following SQL fiddle, but replaced your long union subquery with a static copy of your data: http://sqlfiddle.com/#!18/7907d4/1

CREATE TABLE T_Items (
  ChangeDate date          NOT NULL,
  Product    nvarchar(200) NOT NULL,
  Cost       money         NOT NULL
)

INSERT INTO T_Items SELECT '01/01/2000', 'Water', 5;
INSERT INTO T_Items SELECT '06/01/2000', 'Banana', 2;
INSERT INTO T_Items SELECT '09/01/2000', 'orange', 2;
INSERT INTO T_Items SELECT '10/01/2000', 'Water', 3;
INSERT INTO T_Items SELECT '01/01/2000', 'Banana', 2.5;
INSERT INTO T_Items SELECT '08/01/2000', 'Water', 6;
INSERT INTO T_Items SELECT '03/01/2000', 'Banana', 1;
INSERT INTO T_Items SELECT '05/01/2000', 'Water', 3;
INSERT INTO T_Items SELECT '02/01/2000', 'Banana', 3;
INSERT INTO T_Items SELECT '12/01/2000', 'orange', 4;

SELECT TOP 1 WITH TIES
   ChangeDate,
   Product,
   Cost
FROM
  T_Items
ORDER BY ROW_NUMBER() OVER (PARTITION BY Product ORDER BY ChangeDate DESC);

You can replace the part T_Items with your union subquery making it look like this:

SELECT TOP 1 WITH TIES
   ChangeDate,
   Product,
   Cost
FROM
  (select  LastCostDate,
    [LocStock].SiteID,      
    [LastDate].PLUID,[...]) AS d1
ORDER BY ROW_NUMBER() OVER (PARTITION BY Product ORDER BY ChangeDate DESC)

Do not forget the AS d1 part. It's mandatory when using subqueries.

If I see this correctly, the tables you provided do not match your posted query as the column names differ (LastCostDate versus ChangeDate). It looks more like you posted the result of your INNER JOIN query:

select [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description, sum([PLUCostHistory].Cost) as Cost From [PLUCostHistory] Inner Join [PLU]
        on [PLU].PLUID=[PLUCostHistory].PLUID group by [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description ) as LastCost

So please adjust your question if this is not what you looked for.