OraFAQ Forum: SQL & PL/SQL » Facing Issue with LAST_VALUE function.

Home » SQL & PL/SQL » SQL & PL/SQL » Facing Issue with LAST_VALUE function. (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Facing Issue with LAST_VALUE function. [message #615944]

Wed, 11 June 2014 06:03

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Hi All,

I'm facing some issue with LAST_VALUE analytical function. May be understanding is wrong. I'm not getting desired output from LAST_VALUE function. Please correct me.

CREATE TABLE MYDATA
(	
    MYORDER   VARCHAR2(1), 
    MYVAL     NUMBER(2), 
) ;

Insert into MYDATA (MYORDER,MYVAL) values ('A',1);
Insert into MYDATA (MYORDER,MYVAL) values ('A',1);
Insert into MYDATA (MYORDER,MYVAL) values ('A',1);
Insert into MYDATA (MYORDER,MYVAL) values ('A',2);
Insert into MYDATA (MYORDER,MYVAL) values ('A',2);
Insert into MYDATA (MYORDER,MYVAL) values ('A',3);
Insert into MYDATA (MYORDER,MYVAL) values ('A',3);
Insert into MYDATA (MYORDER,MYVAL) values ('A',4);
Insert into MYDATA (MYORDER,MYVAL) values ('A',4);
Insert into MYDATA (MYORDER,MYVAL) values ('B',10);
Insert into MYDATA (MYORDER,MYVAL) values ('B',10);
Insert into MYDATA (MYORDER,MYVAL) values ('B',2);
Insert into MYDATA (MYORDER,MYVAL) values ('B',2);
Insert into MYDATA (MYORDER,MYVAL) values ('B',3);
Insert into MYDATA (MYORDER,MYVAL) values ('B',3);

SELECT 
      B.MYORDER
     ,B.MYVAL
    ,MAX( B.MYVAL ) OVER( ORDER BY B.MYORDER, B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) MAXVALUE
    ,LAST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYORDER, B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) LASTVALUE
    ,FIRST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYORDER, B.MYVAL ROWS UNBOUNDED PRECEDING ) FIRSTVALUE
FROM BHIMFINAL B 
ORDER BY 1,2 ;

Received Output.

MYORDER	MYVAL	MAXVALUE	LASTVALUE	FIRSTVALUE
A	1	1	1	1
A	1	1	1	1
A	1	1	1	1
A	2	2	2	1
A	2	2	2	1
A	3	3	3	1
A	3	3	3	1
A	4	4	4	1
A	4	4	4	1
B	2	4	2	1
B	2	4	2	1
B	3	4	3	1
B	3	4	3	1
B	10	10	10	1
B	10	10	10	1

Expected Output

MYORDER	MYVAL	MAXVALUE	LASTVALUE	FIRSTVALUE
A	1	1	1	1
A	1	1	1	1
A	1	1	1	1
A	2	2	2	1
A	2	2	2	1
A	3	3	3	1
A	3	3	3	1
A	4	4	4	1
A	4	4	4	1
B	2	4	4	1
B	2	4	4	1
B	3	4	4	1
B	3	4	4	1
B	10	10	10	1
B	10	10	10	1

In this situation I'm expecting MAX and LAST_VALUE functions to return same values. Although FIRST_VALUE is giving desired output.

Please help me to understand.

Thanks & Regards
Manoj

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615946 is a reply to message #615944]

Wed, 11 June 2014 06:13

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why do you expect them to give the same values?

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615947 is a reply to message #615944]

Wed, 11 June 2014 06:14

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why MAX and LAST should be the same?
In your case, LAST is always the same as MYVAL as your window covers all preceding rows up to and including the current one, so the current one is the last one.

Note: could you, PLEASE, align your columns.

[Updated on: Wed, 11 June 2014 06:14]

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615955 is a reply to message #615947]

Wed, 11 June 2014 07:01

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Michel Cadot wrote on Wed, 11 June 2014 16:44

In your case, LAST is always the same as MYVAL as your window covers all preceding rows up to and including the current one, so the current one is the last one.

Yes, Michel you are right. In this case window is from first record to current record in which current is included. But I'm using order by in analytical function. My understanding is records of a window will be ordered and then LAST_VALUE will be returned out of those. Am I right?

Received Output.

MYORDER	MYVAL	MAXVALUE	LASTVALUE	FIRSTVALUE
A	1	1		1		1
A	1	1		1		1
A	1	1		1		1
A	2	2		2		1
A	2	2		2		1
A	3	3		3		1
A	3	3		3		1
A	4	4		4		1
A	4	4		4		1
B	2	4		2		1
B	2	4		2		1
B	3	4		3		1
B	3	4		3		1
B	10	10		10		1
B	10	10		10		1

Expected Output.

MYORDER	MYVAL	MAXVALUE	LASTVALUE	FIRSTVALUE
A	1	1		1		1
A	1	1		1		1
A	1	1		1		1
A	2	2		2		1
A	2	2		2		1
A	3	3		3		1
A	3	3		3		1
A	4	4		4		1
A	4	4		4		1
B	2	4		4		1
B	2	4		4		1
B	3	4		4		1
B	3	4		4		1
B	10	10		10		1
B	10	10		10		1

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615956 is a reply to message #615955]

Wed, 11 June 2014 07:11

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

My understanding is records of a window will be ordered and then LAST_VALUE will be returned out of those. Am I right?

Yes, and the last value is the current one, just see your output as you display the row in the same order than the order of LAST_VALUE.
For instance, B/2 is after A/4 in the order you gave (B.MYORDER, B.MYVAL) and so the last value is 2.
Explain why do you expect 4.

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615961 is a reply to message #615956]

Wed, 11 June 2014 07:17

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Now, I've changed Order By clause for LAST_VALUE. It should give me 4 instead of 2 and 3 because I'm not using partition by so whole query set is treated as single partition. Window is from first row to current row. Order by is only MYVAL. It should give me 4 as 4 will become last value of ordered window.

SELECT 
      B.MYORDER
     ,B.MYVAL
    ,MAX( B.MYVAL ) OVER( ORDER BY B.MYORDER, B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) MAXVALUE
    ,LAST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) LASTVALUE
    ,FIRST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL ROWS UNBOUNDED PRECEDING ) FIRSTVALUE
FROM BHIMFINAL B 
ORDER BY 1,2 ;

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615962 is a reply to message #615956]

Wed, 11 June 2014 07:20

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

To get the values to agree you would need to order everything by myval and then myorder, which renders myorder pretty much pointless.

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615964 is a reply to message #615961]

Wed, 11 June 2014 07:26

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

It should give me 4 as 4 will become last value of ordered window.

Where?

And, PLEASE, post a VALID test case and post queries that match your test case:

SQL> CREATE TABLE MYDATA
  2  (
  3      MYORDER   VARCHAR2(1),
  4      MYVAL     NUMBER(2),
  5  ) ;
)
*
ERROR at line 5:
ORA-00904: : invalid identifier

SQL> SELECT
  2        B.MYORDER
  3       ,B.MYVAL
  4      ,MAX( B.MYVAL ) OVER( ORDER BY B.MYORDER, B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) MAXVALUE
  5      ,LAST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) LASTVALUE
  6      ,FIRST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL ROWS UNBOUNDED PRECEDING ) FIRSTVALUE
  7  FROM BHIMFINAL B
  8  ORDER BY 1,2 ;
FROM BHIMFINAL B
     *
ERROR at line 7:
ORA-00942: table or view does not exist

It is boring to waste time which such things.

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615970 is a reply to message #615961]

Wed, 11 June 2014 07:30

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Manoj.Gupta.91 wrote on Wed, 11 June 2014 08:17

Now, I've changed Order By clause for LAST_VALUE.

But you didn't change ORDER BY clause. If youdo, you'll get:

SQL> SELECT 
  2        B.MYORDER
  3       ,B.MYVAL
  4      ,MAX( B.MYVAL ) OVER( ORDER BY B.MYORDER, B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) MAXVALUE
  5      ,LAST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) LASTVALUE
  6      ,FIRST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL ROWS UNBOUNDED PRECEDING ) FIRSTVALUE
  7  FROM MYDATA B
  8  ORDER BY 2
  9  /

M      MYVAL   MAXVALUE  LASTVALUE FIRSTVALUE
- ---------- ---------- ---------- ----------
A          1          1          1          1
A          1          1          1          1
A          1          1          1          1
B          2          4          2          1
A          2          2          2          1
A          2          2          2          1
B          2          4          2          1
B          3          4          3          1
A          3          3          3          1
B          3          4          3          1
A          3          3          3          1

M      MYVAL   MAXVALUE  LASTVALUE FIRSTVALUE
- ---------- ---------- ---------- ----------
A          4          4          4          1
A          4          4          4          1
B         10         10         10          1
B         10         10         10          1

15 rows selected.

SQL>

SY.

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615971 is a reply to message #615964]

Wed, 11 June 2014 07:31

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

CREATE TABLE MYDATA
(	
    MYORDER   VARCHAR2(1), 
    MYVAL     NUMBER(2)
) ;

Insert into MYDATA (MYORDER,MYVAL) values ('A',1);
Insert into MYDATA (MYORDER,MYVAL) values ('A',1);
Insert into MYDATA (MYORDER,MYVAL) values ('A',1);
Insert into MYDATA (MYORDER,MYVAL) values ('A',2);
Insert into MYDATA (MYORDER,MYVAL) values ('A',2);
Insert into MYDATA (MYORDER,MYVAL) values ('A',3);
Insert into MYDATA (MYORDER,MYVAL) values ('A',3);
Insert into MYDATA (MYORDER,MYVAL) values ('A',4);
Insert into MYDATA (MYORDER,MYVAL) values ('A',4);
Insert into MYDATA (MYORDER,MYVAL) values ('B',10);
Insert into MYDATA (MYORDER,MYVAL) values ('B',10);
Insert into MYDATA (MYORDER,MYVAL) values ('B',2);
Insert into MYDATA (MYORDER,MYVAL) values ('B',2);
Insert into MYDATA (MYORDER,MYVAL) values ('B',3);
Insert into MYDATA (MYORDER,MYVAL) values ('B',3);

SELECT 
      B.MYORDER
     ,B.MYVAL
    ,MAX( B.MYVAL ) OVER( ORDER BY B.MYORDER, B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) MAXVALUE
    ,LAST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) LASTVALUE
    ,FIRST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL ROWS UNBOUNDED PRECEDING ) FIRSTVALUE
FROM MYDATA B 
ORDER BY 1,2 ;

Expected Output :

MYORDER	MYVAL	MAXVALUE	LASTVALUE	FIRSTVALUE
A	1	1		1		1
A	1	1		1		1
A	1	1		1		1
A	2	2		2		1
A	2	2		2		1
A	3	3		3		1
A	3	3		3		1
A	4	4		4		1
A	4	4		4		1
B	2	4		4		1
B	2	4		4		1
B	3	4		4		1
B	3	4		4		1
B	10	10		10		1
B	10	10		10		1

In output records with A should appear first and then records for B. But for LAST_VALUE column maximum value till current row should be returned.

[Updated on: Wed, 11 June 2014 07:36]

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615974 is a reply to message #615971]

Wed, 11 June 2014 07:34

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Solomon gives you the reason of your misunderstanding.
To understand how analytical functions work you have to display the data in the same order than you order them in the functions, otherwise it is very difficult to see.

Also:
CREATE TABLE MYDATA
FROM BHIMFINAL B

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615979 is a reply to message #615970]

Wed, 11 June 2014 07:46

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Having played around with this it seems last_value completely ignores partition by and order by clauses if the windowing clause ends in current row, is that right?

SQL> SELECT
  2        B.MYORDER
  3       ,B.MYVAL
  4      ,LAST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) LAST
  5      ,LAST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) LAST_desc
  6      ,LAST_VALUE( B.MYVAL ) OVER( PARTITION BY myorder ORDER BY B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) LAST_par
  7      ,LAST_VALUE( B.MYVAL ) OVER( PARTITION BY myorder ORDER BY B.MYVAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) LASt_par_desc
  8      ,LAST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) LAST_unb
  9      ,LAST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LAST_unb_desc
 10      ,LAST_VALUE( B.MYVAL ) OVER( PARTITION BY myorder ORDER BY B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LAST_unb_par
 11      ,LAST_VALUE( B.MYVAL ) OVER( PARTITION BY myorder ORDER BY B.MYVAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) LASt_und_par_desc
 12  FROM MYDATA  B
 13  ORDER BY 1,2 ;
 
MYORDER MYVAL       LAST  LAST_DESC   LAST_PAR LAST_PAR_DESC   LAST_UNB LAST_UNB_DESC LAST_UNB_PAR LAST_UND_PAR_DESC
------- ----- ---------- ---------- ---------- ------------- ---------- ------------- ------------ -----------------
A           1          1          1          1             1         10             1            4                 1
A           1          1          1          1             1         10             1            4                 1
A           1          1          1          1             1         10             1            4                 1
A           2          2          2          2             2         10             1            4                 1
A           2          2          2          2             2         10             1            4                 1
A           3          3          3          3             3         10             1            4                 1
A           3          3          3          3             3         10             1            4                 1
A           4          4          4          4             4         10             1            4                 1
A           4          4          4          4             4         10             1            4                 1
B           2          2          2          2             2         10             1           10                 2
B           2          2          2          2             2         10             1           10                 2
B           3          3          3          3             3         10             1           10                 2
B           3          3          3          3             3         10             1           10                 2
B          10         10         10         10            10         10             1           10                 2
B          10         10         10         10            10         10             1           10                 2
 
15 rows selected
 
SQL>

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615983 is a reply to message #615979]

Wed, 11 June 2014 07:54

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

cookiemonster wrote on Wed, 11 June 2014 08:46

Having played around with this it seems last_value completely ignores partition by and order by clauses if the windowing clause ends in current row, is that right?

And how did you come to this conclusion???

SY.

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615986 is a reply to message #615983]

Wed, 11 June 2014 08:05

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

With the query above obviously, the first 4 last_values all give exactly the same answer. if I'm missing something point it out

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615988 is a reply to message #615979]

Wed, 11 June 2014 08:18

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

cookiemonster wrote on Wed, 11 June 2014 14:46

Having played around with this it seems last_value completely ignores partition by and order by clauses if the windowing clause ends in current row, is that right?

I think you are right as the window starts from the 1st row (of the partition for the given order) and ends with the current row so the last value is always the current value.
This means this function is useful only if you give something different from the default window (which is equivalent to the one given here)... or when you have nulls and you give the "ignore nulls" option.

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615993 is a reply to message #615988]

Wed, 11 June 2014 08:32

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

No, I don't think cookiemonster is right. The fact LAST_VALUE(X) OVER(ORDER BY X) or LAST_VALUE(X) OVER(PARTITION BY Y ORDER BY X) always produces current row X doesn't mean partition and/or order by are ignored. It is same as saying POWER(1,N) is ignored since it always returns 1.

SY.

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615996 is a reply to message #615993]

Wed, 11 June 2014 08:41

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I didn't mean it is ignored in the execution, I meant it just returns the current row value because it is the last one in this case.
Maybe my low level in English makes me understand something that is not in the sentence (I have a fuzzy understanding module so what I read is not always what is written; your example with POWER makes me understand something I first didn't see. Smile

Smile

)

[Updated on: Wed, 11 June 2014 08:43]

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #615998 is a reply to message #615996]

Wed, 11 June 2014 08:47

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Right, it will produce current row value however optimizer still does the partitioning and ordering (for nothing since it isn't smart enough to ignore it and simply return current row value).

SY.

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #616001 is a reply to message #615998]

Wed, 11 June 2014 08:58

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Good info, never thought about it.

Is there any way to stop optimizer from going for a ride for partitioning and ordering? Like...hint the optimizer to ignore from doing the overhead activity when we have just one row or rewrite the query. Well it shouldn't be much of a overhead though for just one row.

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #616003 is a reply to message #616001]

Wed, 11 June 2014 09:02

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

There is. Hint is called "problem is between the chair and keybord" Laughing

Laughing

SY.

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #616005 is a reply to message #616001]

Wed, 11 June 2014 09:08

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

None I knows but maybe one day the optimizer will recognize it as it currently recognizes the ORDER BY clause inside the analytical function and compares it to the one of the query and does not execute this later one if it is the same one than in the function:

SQL> @xpl3p

  2  select ename, row_number() over (order by ename) from emp order by ename;

Explained.

SQL> @xpl3
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3145491563

---------------------------------------------------------
| Id  | Operation          | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     14 |     3   (0)|
|   1 |  WINDOW SORT       |      |     14 |     3   (0)|
|   2 |   TABLE ACCESS FULL| EMP  |     14 |     3   (0)|
---------------------------------------------------------

Compare to (not the same order):

SQL> @xpl3p

  2  select ename, row_number() over (order by ename) from emp order by ename desc;

Explained.

SQL> @xpl3
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3946901873

----------------------------------------------------------
| Id  | Operation           | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     14 |     5  (40)|
|   1 |  SORT ORDER BY      |      |     14 |     5  (40)|
|   2 |   WINDOW SORT       |      |     14 |     5  (40)|
|   3 |    TABLE ACCESS FULL| EMP  |     14 |     3   (0)|
----------------------------------------------------------

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #616008 is a reply to message #616005]

Wed, 11 June 2014 09:33

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Michel Cadot wrote on Wed, 11 June 2014 19:38

Compare to (not the same order):

Yes, SORT ORDER BY took precedence. Thanks Michel.

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #616042 is a reply to message #616008]

Wed, 11 June 2014 22:56

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Hi All,

What I understand from above replies there seems some bug in LAST_VALUE analytical function when used with window clause as it ignores ORDER BY clause for window. Right?

Regards
Manoj

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #616047 is a reply to message #616042]

Thu, 12 June 2014 00:15

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No, it does not, it perfectly works.

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #616048 is a reply to message #616047]

Thu, 12 June 2014 00:28

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Then I'm seriously confused.........

Can you please explain in simple way why window order by clause is not working?

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #616049 is a reply to message #616048]

Thu, 12 June 2014 00:33

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is working, could you explain why you think it does not work?

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #616051 is a reply to message #616049]

Thu, 12 June 2014 01:05

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Hi All,

Let's explain this step by step.

SELECT 
      B.MYORDER
     ,B.MYVAL
    ,MAX( B.MYVAL ) OVER( ORDER BY B.MYORDER, B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) MAXVALUE
    ,LAST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) LASTVALUE
    ,FIRST_VALUE( B.MYVAL ) OVER( ORDER BY B.MYVAL ROWS UNBOUNDED PRECEDING ) FIRSTVALUE
FROM MYDATA B 
ORDER BY 1 ;

Since I'm not using any PARTITION BY clause so entire query set is a partition.

Expectation is it will sort all records of a window for analytical function and then return respective value out of that sorted set.

ROWNUM	MYORDER	MYVAL	MAXVALUE	LASTVALUE	FIRSTVALUE
1	A	1	1		1		1     --Window is only for this row so same value is returned.
2	A	1	1		1		1     --Window from Row 1 to 2. Ordered last value is 1.
3	A	1	1		1		1     --Window from Row 1 to 3. Ordered last value is 1.
4	A	2	2		2		1     --Window from Row 1 to 4. Ordered last value is 2.
5	A	2	2		2		1     --Window from Row 1 to 5. Ordered last value is 2.
6	A	3	3		3		1     --Window from Row 1 to 6. Ordered last value is 3.
7	A	3	3		3		1     --Window from Row 1 to 7. Ordered last value is 3.
8	A	4	4		4		1     --Window from Row 1 to 8. Ordered last value is 4.
9	A	4	4		4		1     --Window from Row 1 to 9. Ordered last value is 4.
10	B	2	4		2		1     --Window from Row 1 to 10. Ordered last value should be 4.
11	B	2	4		2		1     --Window from Row 1 to 11. Ordered last value should be 4.
12	B	3	4		3		1     --Window from Row 1 to 12. Ordered last value should be 4.
13	B	3	4		3		1     --Window from Row 1 to 13. Ordered last value should be 4.
14	B	10	10		10		1     --Window from Row 1 to 14. Ordered last value should be 10.
15	B	10	10		10		1     --Window from Row 1 to 15. Ordered last value should be 10.

For eg. while processing rownum=10 window is from rownum=1 to rownum=10. If we sort records these records for MYVAL (default ascending order) we are supposed to get 4 and same is with rownum=11,12 and 13.

while processing rownum=14 window is from rownum=1 to rownum=14. If we sort records these records for MYVAL (default ascending order) we are supposed to get 10 and same is with rownum=15.

I'm not supposed to get LAST_VALUE without sorting window's records.

Please point me exactly where my understanding is wrong.

Thanks & Regards
Manoj

[Updated on: Thu, 12 June 2014 01:05]

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #616054 is a reply to message #616051]

Thu, 12 June 2014 01:23

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

As already said, the output is NOT ordered in the same way the rows are ordered in the LAST_VALUE function, so you can't apply the function in this former order as it is actually NOT apply in this former order but in the later one.
If you want to see how the LAST_VALUE function works you MUST order the displayed result set in the same way you order the result set in the LAST_VALUE function.
so instead of ORDER BY 1 (which is B.MYORDER), you MUST use ORDER BY B.MYVAL.

[Updated on: Thu, 12 June 2014 01:24]

Report message to a moderator

Re: Facing Issue with LAST_VALUE function. [message #616058 is a reply to message #616054]

Thu, 12 June 2014 02:00

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Got it now......!!

Example was really confusing so trapped in a confusing situation. Now it's clear.

Thanks to all of you for your help.

Regards
Manoj

Report message to a moderator

Previous Topic:	Purpose of ODCIAggregateMerge
Next Topic:	SQL Data preparation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 21:37:11 CDT 2024