Group by Latest value

Question

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!

Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — ggordon, Oct 25 '21 at 13:47

score 0 · Answer 1 · answered Oct 25 '21 at 13:43

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

score 0 · Accepted Answer · answered Oct 25 '21 at 13:46

0

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"

answered Oct 25 '21 at 13:46

p_efros

301
2
11

thanks i just deleted the original_table alias and it worked! – no name sry Oct 26 '21 at 07:17

Group by Latest value

2 Answers2