LAST_VALUE function causing error: “Invalid in the select list…” SQL

I need to pull the most recent value for a given field as dictated by entry date. When I try to use LAST_VALUE function, I get the following error message “An Error occurred while executing the query. Column ‘SCAObservation.ObsDtm’ is invalid in the select list because it is not contained either in an aggregate function or the GROUP BY clause”. ‘SCAObservation.ObsDtm’ is aggregated in the select list but not the LAST_VALUE function. When I add it to the GROUP BY clause, the data does not pull correctly. What am I missing? Here is some of the code:

SELECT
,MAX(SCAObservation.ObsDtm) AS Max_ObsDtm
,MIN(SCAObservation.ObsDtm) AS Min_ObsDtm
,COUNT(DISTINCT SCAObservation.ObsDtm) AS Count_ObsDtm
,LAST_VALUE(SCAObservation.ObsValue) OVER (ORDER BY SCAObservation.ObsDtm ASC) AS Max_ObsValue

Answer

LAST_VALUE() is a window function, not an aggregation function. You can do what you want as:

SELECT DISTINCT MAX(o.ObsDtm) OVER () AS Max_ObsDtm,
       MIN(o.ObsDtm) OVER () AS Min_ObsDtm,
       COUNT(DISTINCT o.ObsDtm) OVER () AS Count_ObsDtm,
       LAST_VALUE(o.ObsValue) OVER (ORDER BY o.ObsDtm ASC) AS Max_ObsValue
FROM SCAObservation o;

However, that won’t do what you really want. I suspect you want conditional aggregation:

SELECT MAX(o.ObsDtm AS Max_ObsDtm,
       MIN(o.ObsDtm)AS Min_ObsDtm,
       COUNT(DISTINCT o.ObsDtm) AS Count_ObsDtm,
       MAX(CASE WHEN seqnum = 1 THEN o.ObsValue END) AS Max_ObsValue
FROM (SELECT o.*, ROW_NUMBER() OVER (ORDER BY o.ObsDtm) as seqnum
      FROM SCAObservation o
     ) o;