CODEWITHSUNDEEP
sql
no name sry
no name sry

Reputation: 27

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?

Upvotes: 0

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

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";

Upvotes: 2

Reza Esmaeli
Reza Esmaeli

Reputation: 160

you can pivote

SELECT * FROM 
(
  SELECT 
    'LatestDateMetric'+cast(MetricID as nvarchar) as MetricID, 
    max(Date) as Date,
    Instance as Instance
  FROM API_Metric2
  group by Instance,MetricID
) t 
PIVOT(
MAX(Date) 
FOR MetricID IN (
    [LatestDateMetric1], 
    [LatestDateMetric2], 
    [LatestDateMetric3])
) AS pivot_table;

Upvotes: 0

Related Questions