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

asked 28 Aug '14, 12:23

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.5k233560
accept rate: 15%

retagged 29 Aug '14, 12:13

Nica%20_SAP's gravatar image

Nica _SAP
866722


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

permanent link

answered 28 Aug '14, 17:13

Mark%20Culp's gravatar image

Mark Culp
22.9k9129269
accept rate: 40%

Thank you for checking this out for me. I'm glad I could bring up a non trivial question. I understand now why it is not working as I expected.

Enhancement Request: Create a First_Value() version that can be used in a normal group by situation. Syntax could be like LIST( ... order by ....).

(29 Aug '14, 02:28) Thomas Dueme...

I had to talk to a QP expert to figure out the above answer.

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

(29 Aug '14, 04:29) Volker Barth
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

permanent link

answered 29 Aug '14, 12:07

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

edited 29 Aug '14, 12:10

I have not embraced the new Array capabilities. Thank for that great idea.

(29 Aug '14, 12:20) Thomas Dueme...
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×14
×7
×6
×5
×2

question asked: 28 Aug '14, 12:23

question was seen: 1,049 times

last updated: 01 Sep '14, 06:19