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!
Advertisement
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"