Home » SQL & PL/SQL » SQL & PL/SQL » last_value not working in Oracle 9i (Oracle 9i)
icon7.gif  last_value not working in Oracle 9i [message #441039] Thu, 28 January 2010 06:53 Go to next message
argancel
Messages: 1
Registered: January 2010
Location: Mauritius
Junior Member
Hi everyone,

I have a strange issue:

When I execute the following query :

SELECT a,b,c,first_value(a) over (PARTITION BY b ORDER BY c) d FROM
(SELECT 1 a, 2 b, 1 c FROM dual
UNION ALL
SELECT 2 a, 2 b, 3 c FROM dual
UNION ALL
SELECT 3 a, 2 b, 2 c FROM dual
UNION ALL
SELECT 4 a, 3 b, 2 c FROM dual
UNION ALL
SELECT 5 a, 3 b, 1 c FROM dual
)

I get the correct result from oracle, that is:
A | B | C | D
--------------------------
1 | 2 | 1 | 1
3 | 2 | 2 | 1
2 | 2 | 3 | 1
5 | 3 | 1 | 5
4 | 3 | 2 | 5

However, if i replace the "first_value" clause by "last_value" i get this, which is completly wrong:

A | B | C | D
--------------------------
1 | 2 | 1 | 1
3 | 2 | 2 | 3
2 | 2 | 3 | 2
5 | 3 | 1 | 5
4 | 3 | 2 | 4

Do you know if this is an Oracle bug?
Re: last_value not working in Oracle 9i [message #441045 is a reply to message #441039] Thu, 28 January 2010 07:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is expected behaviour. It shows the last value of a over the rows processed so far. (i.e. the value of the current row)
If you want different behaviour, change the window clause (UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
Re: last_value not working in Oracle 9i [message #441047 is a reply to message #441039] Thu, 28 January 2010 07:15 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
Hi,
this is no bug, but expected behaviour, as documented in SQL Reference.
For 9iR2, analytic functions are described here: http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions2a.htm#81409
Nearly in the end, you may find this: Quote:
If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Which is your case - LAST_VALUE is not taken from rows "behind" the current one. If you want to have LAST_VALUE from all rows in given PARTITION, just use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (see e.g. example for LAST_VALUE function).
Re: last_value not working in Oracle 9i [message #441048 is a reply to message #441039] Thu, 28 January 2010 07:15 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Previous Topic: update query for oracle
Next Topic: using column name returned by nested select
Goto Forum:
  


Current Time: Sat Sep 24 16:21:48 CDT 2016

Total time taken to generate the page: 0.08952 seconds