How can I find all Sites with latest date?

I am trying to add all areas of data that will show all latest pricing. Because there are two latest dates for specific locations, only 5/6 show up with a date of 5/27. I need one location to show up as well as the latest date for it is 5/26. EXAMPLE BELOW:

ITEMNUMBER PRICETYPE FROMDATE    SITE PRICECREATEDDATE PRICE PRICEQUANTITY
12345      cost      2022-05-26   A    2022-05-26       1.23  1
12345      cost      2022-05-27   G    2022-05-27       1.23  1
12345      cost      2022-05-27   M    2022-05-27       1.23  1
12345      cost      2022-05-27   N    2022-05-27       1.23  1
12345      cost      2022-05-27   P    2022-05-27       1.23  1
12345      cost      2022-05-27   U    2022-05-27       1.23  1

I currently have this query:

SELECT T.ITEMNUMBER, T.PRICETYPE, T.FROMDATE, T.SITE, T.PRICECREATEDDATE, T.PRICE, T.PRICEQUANTITY
FROM DBO.[D365 STRD Costs] T
INNER JOIN (
    SELECT ITEMNUMBER, MAX(FROMDATE) AS MAXDATE
    FROM DBO.[D365 STRD Costs]
    GROUP BY ITEMNUMBER
) TM ON T.ITEMNUMBER = TM.ITEMNUMBER AND T.FROMDATE = TM.MAXDATE

RESULTS:

ITEMNUMBER PRICETYPE FROMDATE    SITE PRICECREATEDDATE PRICE PRICEQUANTITY
12345      cost      2022-05-27   G    2022-05-27       1.23  1
12345      cost      2022-05-27   M    2022-05-27       1.23  1
12345      cost      2022-05-27   N    2022-05-27       1.23  1
12345      cost      2022-05-27   P    2022-05-27       1.23  1
12345      cost      2022-05-27   U    2022-05-27       1.23  1

I need SITE “A” to pull up as well since that is the latest price. It was just imported a day before.

Answer

You seem to be using SQL Server (or Sybase) instead of MySQL.

Try adding SITE to the GROUP BY if you need that to be included:

SELECT T.ITEMNUMBER, T.PRICETYPE, T.FROMDATE, T.SITE, T.PRICECREATEDDATE, T.PRICE, T.PRICEQUANTITY
FROM DBO.[D365 STRD Costs] t
INNER JOIN (
    SELECT ITEMNUMBER, SITE, MAX(FROMDATE) AS MAXDATE
    FROM DBO.[D365 STRD Costs]
    GROUP BY ITEMNUMBER, SITE
) TM ON T.ITEMNUMBER = TM.ITEMNUMBER AND T.FROMDATE = TM.MAXDATE 
AND T.SITE = TM.SITE