Home » SQL & PL/SQL » SQL & PL/SQL » first_ value and last_value errors
first_ value and last_value errors [message #222506] Mon, 05 March 2007 04:25 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
plz help me
1.select * from (select FIRST_VALUE(empno) over(partition by deptno order by empno) as empno,
FIRST_VALUE(deptno) over(partition by deptno order by empno) as deptno from emp)

2.select * from (select last_VALUE(empno) over(partition by deptno order by empno) as empno,
last_VALUE(deptno) over(partition by deptno order by empno) as deptno from emp)

1st giving proper results

but 2nd query is not proper results.

any problem in last_value

what is problem

Thanks,
srinivas




Re: first_ value and last_value errors [message #222519 is a reply to message #222506] Mon, 05 March 2007 05:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What results are you getting, and what results do you think you should be getting?

A CREATE TABLE statement and some INSERTs to generate test data would be nice.
Re: first_ value and last_value errors [message #222539 is a reply to message #222519] Mon, 05 March 2007 05:46 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

i want last value of empno

iam geting all values
Re: first_ value and last_value errors [message #222553 is a reply to message #222539] Mon, 05 March 2007 07:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Point 1: If you can't be bothered to do anything other than cut and paste a screen shot, then I can't be bothered to help you.

Point 2: In my previous post, I asked
What results are you getting, and what results do you think you should be getting?
. Now you've only included one screen shot, so even if I could be bothered studying your screenshot, I'd still be short of half of the information.

So, here's what to provide if you want me to keep looking at this:

1) A description, preferably including results cut and paste from SQL*Plus (and formatted in CODE tags) showing the results that you get that you think are wrong.

2) A description of what you think the correct results would be.

3) (I have no actual hope you'll do this, but I'll ask anyway) A CREATE table or Describe, and a set of INSERT statements for the data that you use for this problem.

I'm sure you think that this sounds like I'm asking you to do a lot, but you're the one who has a problem that needs solving, and I'm the one being asked to solve it for free, so I think I'm on pretty safe ground here.

No attachments either, please.
Re: first_ value and last_value errors [message #222572 is a reply to message #222553] Mon, 05 March 2007 08:02 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
observe the quries result,
iam using first_value, iam getting first values of empnos for deptno. its fine. its my proper result.

iam using last_value, iam not getting last values of empnos. its not my proper result. i want last values.
what is the problem


SQL> select * from (select FIRST_VALUE(empno) over(partition by deptno order by empno) as empno,
2 FIRST_VALUE(deptno) over(partition by deptno order by empno) as deptno from emp)
3 ;

EMPNO DEPTNO
---------- ----------
7782 10
7782 10
7782 10
7369 20
7369 20
7369 20
7369 20
7369 20
7499 30
7499 30
7499 30

EMPNO DEPTNO
---------- ----------
7499 30
7499 30
7499 30

14 rows selected.

SQL> select * from (select last_VALUE(empno) over(partition by deptno order by empno) as empno,
2 last_VALUE(deptno) over(partition by deptno order by empno) as deptno from emp)
3 ;

EMPNO DEPTNO
---------- ----------
7782 10
7839 10
7934 10
7369 20
7566 20
7788 20
7876 20
7902 20
7499 30
7521 30
7654 30

EMPNO DEPTNO
---------- ----------
7698 30
7844 30
7900 30

14 rows selected.


i think u r understood.
thanks
srinivas
Re: first_ value and last_value errors [message #222582 is a reply to message #222572] Mon, 05 March 2007 09:24 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I see there's no SQL to allow me to run tests using your data, but hey - why make things easy for me?

There is no problem here, everything is working exactly according to specification.

From here, you will see that there are 3 clauses to specify for an analytic function - PARTITION BY, ORDER BY and the WINDOWING clause.
The last one is what is causing your confusion. It defines the set of rows over which the analytic function operates, and it's default value is 'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW', which means that for each row of the ordered set defined by the PARTITION and ORDER BY clause, the analytic function is evaluated over all the rows between the first in value and the current value.
Thus, LAST_VALUE will always return the current row in your case, as that will be the last row in the ordered set.

Here is a test case (of the sort I was hoping you'd be good enough to provide) to demonstrate the point:
create table lastval_test (col_1 number, col_2 number);

insert into lastval_test values (10,4);
insert into lastval_test values (10,2);
insert into lastval_test values (10,3);
insert into lastval_test values (10,1);
insert into lastval_test values (20,7);
insert into lastval_test values (20,3);
insert into lastval_test values (20,5);
insert into lastval_test values (20,1);
insert into lastval_test values (30,8);
insert into lastval_test values (30,4);
insert into lastval_test values (30,6);
insert into lastval_test values (30,2);

SQL> select col_2
  2        ,last_value(col_2) over (partition by col_1 order by col_2)  raw_lv
  3        ,last_value(col_2) over (partition by col_1 order by col_2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  raw_lv_plus_default
  4        ,last_value(col_2) over (partition by col_1 order by col_2 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  raw_lv_corrected
  5        ,first_value(col_2) over (partition by col_1 order by col_2 desc)  raw_fv      
  6  from   lastval_test;

     COL_2     RAW_LV RAW_LV_PLUS_DEFAULT RAW_LV_CORRECTED     RAW_FV
---------- ---------- ------------------- ---------------- ----------
         1          1                   1                4          4
         2          2                   2                4          4
         3          3                   3                4          4
         4          4                   4                4          4
         1          1                   1                7          7
         3          3                   3                7          7
         5          5                   5                7          7
         7          7                   7                7          7
         2          2                   2                8          8
         4          4                   4                8          8
         6          6                   6                8          8
         8          8                   8                8          8
(apologies for the linesize)

In this example, the RAW_LV column shows quite clearly that for each row, the LAST_VALUE of the set operated on is the current row (COL_2)

The RAW_LV_PLUS_DEFAULT column is simple the RAW_LV column with the default windowing clause added in explicitly, to demonstrate that it makes no difference.

The RAW_LV_CORRECTED column has a difference windowing clause added to it: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (it works the same with ROWS BETWEEN....). This causes each LAST_VALUE to be evaluated over the entire set defined by the ORDER and PARTITION clauses, and gives the result that I think you were expecting.

The RAW_FV column is simply a FIRST_VALUE with a descending order, that also gives the correct answer.
Previous Topic: Slow cursor loop
Next Topic: Similiar to all_tables (Primary Keys Question)
Goto Forum:
  


Current Time: Wed Dec 07 18:51:27 CST 2016

Total time taken to generate the page: 0.21412 seconds