SAP SQL Anywhere

Question

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

BEGIN
declare local temporary table LastValTest( Id integer, UserName varchar(50), modified timestamp );

insert into LastValTest( Id, UserName, modified)
values  ( 1, 'User 1', '2014-08-01 01:00:00' ),
        ( 2, 'User 1', '2014-08-01 01:01:00' ),
        ( 3, 'User 2', '2014-08-01 01:03:00' ),
        ( 2, 'User 2', '2014-08-10 01:00:00' ),
        ( 2, 'User 3', '2014-08-11 01:00:00' );
-- Show the Set
select Id, UserName, modified from LastValTest order by UserName, modified;

-- Test the first_value and last_value function
select  LastValTest.Id,
        LastValTest.UserName,
        LastValTest.modified,
        first_value( LastValTest.UserName) over ( order by LastValTest.Modified desc),
        last_value( LastValTest.UserName) over ( order by LastValTest.Modified )
from    LastValTest;

-- Fix the Result of Last_Value
select  LastValTest.Id,
        LastValTest.UserName,
        LastValTest.modified,
        last_value( LastValTest.UserName) over ( order by LastValTest.Modified RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
from    LastValTest;

-- This is the query i don't get 
select  LastValTest.Id,
        list( LastValTest.UserName),
        list( LastValTest.modified),
        max( LastValTest.modified),
        first_value(LastValTest.UserName) over ( order by LastValTest.Modified desc),
from    LastValTest
group by LastValTest.Id;

END;

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

Aggregate functions summarize data over a group of rows from the database. The groups are formed using the GROUP BY clause of the SELECT statement.

How can is use GROUP BY with the two Functions in Question ?

Thanks for you time Thomas

Accepted Answer

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:

select  LastValTest.Id,
        list( LastValTest.UserName ),
        list( LastValTest.modified ),
        max( LastValTest.modified ),
        first_value(LastValTest.UserName) over ( order by LastValTest.Modified desc )
from    LastValTest
group by LastValTest.Id;

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

HTH

Answer 2

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.

Enjoy!

Ani

SAP SQL Anywhere

Why works FIRST_VALUE and LAST_VALUE not in grouped Queries like other Aggregat functions

Follow this question

Related questions