I only want to show the latest value.

Here is the table:

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:

I'd appreciate if someone help me, thanks!

CodePudding user response：

You can use ROW_NUMBER() to mark the rows you want. Then filtering is tricial.

For example:

select
  InstanceID, Date, TotalProjects
from (
  select *,
    row_number(over partition by InstanceID
               order by InstanceDate desc) as rn
  from BI_InstanceMetric
) x
where rn = 1

CodePudding user response：

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"