The Title tells the Story and i know how to use a subselect to get the needed result. But often I don't get my head around the window functions so I thought it might help to ask the pros about some background information.
I want to find the last UserName that has been modified in the Group 'ID'.
Here is my Test Script
The last query brings the error that UserName needs to be in the 'Group by' clause. I understand that I have to define a order by clause to get a predictable result.
The question is why can I use LIST( 'expression' order by ...) but not FIRST_VALUE function with a 'over ( order by ...'.
The documentation for Aggregate Functions lists the Function FIRST_VALUE and LAST_VALUE and states that
How can is use GROUP BY with the two Functions in Question ?
Thanks for you time Thomas
The problem with your last query is that you are mixing WINDOW aggregates with GROUP BY aggregates. When both WINDOW expressions and GROUP BY are used in the same query the GROUP BY groups are evaluated first and then the WINDOW expressions are evaluated within each group.
So for your last query:
the "first_value(LastValTest.UserName) over (order by LastValTest.Modified desc)" is a WINDOW aggregate expression and is evaluated after the GROUP BY groups are evaluated, and as such the GROUP BY must contain all expressions that are not contained within a GROUP BY aggregate function and includes any references within any WINDOW functions. In this query the LIST() and MAX() functions are being used a GROUP BY aggregates so the only remaining column values are the LastValTest.Id and LastValTest.UserName and LastValTest.Modified (both coming from the last WINDOW expression) values.
Including the LastValTest.UserName and LastValTest.Modified in the GROUP BY is likely not going to give you what you wanted as written ... so you will likely need to rewrite the query to use a subquery to get the ordering of the grouping correct.
Note that some aggregate functions can work both as GROUP BY aggregates and WINDOW aggregates - MIN, MAX, and many others are examples. Other aggregate functions can only work as WINDOW aggregates - FIRST_VALUE, LAST_VALUE are two. (See here for a full list of aggregate functions - I have sent a comment to the doc team to make it more explicit what type of aggregate function each is: i.e. either WINDOW or GROUP BY or both).
Note: I had to talk to a QP expert to figure out the above answer. (Thanks Ani!)
answered 28 Aug '14, 17:13
first and last values in a group can be obtained using array_agg() aggregate function in SQL Anywhere 16.0, or using list() and sa_split_list() before SQL Anywhere 16.0. See the query above for examples.