CODEWITHSUNDEEP
functiont-sqlnetezza
SeaChange
SeaChange

Reputation: 161

Last value function in a grouped Netezza query

I'm trying to use a Last Value function to return the last field, ordered by date, that meets some criteria. However, Netezza keeps telling me that the field I'm trying to order by within the last value function needs to be grouped. I think the problem is that I'm trying to use Last Value alongside some other aggregate functions, but I don't know the solution.

Say I have this data:

CustomerID    Type          Product      Dollars      Date
1             Widget1       Gadget1      100          2016-09-01
1             Widget2       Gadget2      100          2016-09-02
1             Widget1       Gadget3      100          2016-09-03
1             Widget2       Gadget2      100          2016-09-04
2             Widget2       Gadget2      100          2016-09-01

I want to return this result set:

CustomerID    SumOfDollars  LastWidget1
1             400           Gadget3
2             100           NULL

The query I'm using is:

SELECT
    CustomerID
    ,SUM(Dollars) AS SumOfDollars
    ,LAST_VALUE(CASE WHEN Type = 'Widget1' THEN Product ELSE NULL END IGNORE NULLS)
      OVER (ORDER BY Date DESC) AS LastWidget1
FROM Table
GROUP BY CustomerID

When I run this query, Netezza tells me that Date must be grouped (or something along those lines). Can anyone help me out with the solution?

Upvotes: 0

Views: 835

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

LAST_VALUE() is an analytic function, not an aggregation function. You can do this with a subquery:

SELECT CustomerID, SUM(Dollars) AS SumOfDollars,
       MAX(LastWidget1) as LastWidget1
FROM (SELECT t.*,
             LAST_VALUE(CASE WHEN Type = 'Widget1' THEN Product ELSE NULL END IGNORE NULLS)
                 OVER (PARTITION BY CustomerID ORDER BY Date DESC) AS LastWidget1
      FROM Table t
     ) t
GROUP BY CustomerID;

Upvotes: 1

Related Questions