Group by Latest value

I only want to show the latest value.

Here is the table:

InstanceID	InstanceDate	TotalProjects
1	2021-10-14	69
1	2021-10-12	70
2	2021-10-14	20
3	2021-10-14	89
3 ​	2021-10-13	99
3	2021-10-11	100
4 ​	2021-10-12	12
4	2021-10-14	13
5 ​	2021-10-14	0

Here is the query I used to get this table:

SELECT "InstanceID", MAX("Date"), "TotalProjects"
FROM "BI_InstanceMetric"
GROUP BY "InstanceID", "TotalProjects"
ORDER BY "InstanceID"

This is what I want:

InstanceID	InstanceDate	TotalProjects
1	2021-10-14	69
2	2021-10-14	20
3	2021-10-14	89
4	2021-10-14	13
5	2021-10-14	0

I’d appreciate if someone help me, thanks!

Answer

Please fix any syntax errors if present:

SELECT "InstanceID", "InstanceDate", "TotalProjects"
FROM "BI_InstanceMetric" as ORIGINAL_TABLE
JOIN (SELECT "InstanceID", MAX("InstanceDate") as "InstanceDate"
FROM "BI_InstanceMetric"
GROUP BY "InstanceID") as MAX_TABLE
ON MAX_TABLE."InstanceID" = ORIGINAL_TABLE."InstanceID"
AND MAX_TABLE."InstanceDate" = ORIGINAL_TABLE."InstanceDate"