I have an issue with grouping the latest date by metric. Let me show you exactly what I mean, let's say I have this table: Instance Date MetricID Value Marc 09/14/21 1 5 Marc 09/14/21 2 2 Marc 09/14/21 3 1 John 09/14/21 1 10 John 09/14/21 2 1 John 09/14/21 3 1 Marc 09/15/21 1 15 Marc 09/15/21 2

How can I group by Latest Date in SQL?

I have an issue with grouping the latest date by metric.

Let me show you exactly what I mean, let’s say I have this table:

Instance	Date	MetricID	Value
Marc	09/14/21	1	5
Marc	09/14/21	2	2
Marc	09/14/21	3	1
John	09/14/21	1	10
John	09/14/21	2	1
John	09/14/21	3	1
Marc	09/15/21	1	15
Marc	09/15/21	2	0
Marc	09/15/21	3	1
John	09/15/21	1	10
John	09/15/21	2	1
John	09/15/21	3	0

I want to group the instance so that I only get the latest date by Metric if the value is positive (>0).

So I want to have this:

Instance	LatestDateMetric1	LatestDateMetric2	LatestDateMetric3
Marc	09/15/21	09/14/21	09/15/21
John	09/15/21	09/15/21	09/14/21

I already tried grouping by MetricID and Max(Date) but I got an error message.

Any help is appreciated, thanks!

Edit: I tried this code, It looks a bit like I want except It takes the value even if it is null and the result is by line not column.

 SELECT "Instance", "MetricID", MAX("Date") as "LatestDate"
FROM "API_Metric2"
GROUP BY "Instance", "MetricID"

This is the result I got:

Instance	MetricID	LatestDate
Marc	1	09/15/21
Marc	2	09/15/21
Marc	3	09/15/21
John	1	09/15/21
John	2	09/15/21
John	3	09/15/21

How can I make it so that it only takes the value if it is not null?

Answer

You can use conditional aggregation:

SELECT "Instance",
       MAX(CASE WHEN "MetricID" = 1 THEN "Date" END) as LatestDateMetric1,
       MAX(CASE WHEN "MetricID" = 2 THEN "Date" END) as LatestDateMetric2,
       MAX(CASE WHEN "MetricID" = 3 THEN "Date" END) as LatestDateMetric3
FROM "API_Metric2"
WHERE value > 0
GROUP BY "Instance";

Advertisement

Answer