The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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'.

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.

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!)

Mark, that's soothing... - I have had an open question (not yes asked here) on the behaviour of GROUP BY and WINDOW in the same query block for some time, and I guess your explanation seems to have some valuable hints...

select LastValTest.Id,
list( LastValTest.UserName ),
list( LastValTest.modified ),
max( LastValTest.modified ),
-- USING ARRAY_AGG() for first_value(LastValTest.UserName) over ( order by LastValTest.Modified desc )
array_agg(LastValTest.UserName order by LastValTest.Modified desc)[[1]] 'FIRST1',
-- USING sa_split_list() for first_value(LastValTest.UserName) over ( order by LastValTest.Modified desc )
list(LastValTest.UserName order by LastValTest.Modified desc) L,
(select first row_value from sa_split_list(L) order by line_num) 'FIRST2',
-- USING ARRAY_AGG() for last_value(LastValTest.UserName) over ( order by LastValTest.Modified desc )
array_agg(LastValTest.UserName order by LastValTest.Modified desc)[[count(*)]] 'LAST1',
-- USING sa_split_list() for last_value(LastValTest.UserName) over ( order by LastValTest.Modified desc )
(select first row_value from sa_split_list(L) order by line_num DESC) 'LAST2'
from LastValTest
group by LastValTest.Id;

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.