last_value not working in Oracle 9i [message #441039] |
Thu, 28 January 2010 06:53  |
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   |
Frank
Messages: 7901 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 #441048 is a reply to message #441039] |
Thu, 28 January 2010 07:15  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|