Analytic question
40342Apr 5 2006 — edited Apr 14 2006I have the following table
CREATE TABLE TMP1
(CNAME varchar2(64),
PMONTH varchar2(6));
inset into TMp1 ( cname,pmonth ) values ( 'TEST1','200601');
inset into TMp1 ( cname,pmonth ) values ( 'TEST1','200602');
inset into TMp1 ( cname,pmonth ) values ( 'TEST1','200603');
inset into TMp1 ( cname,pmonth ) values ( 'TEST1','200604');
inset into TMp1 ( cname,pmonth ) values ( 'TEST1','200605');
inset into TMp1 ( cname,pmonth ) values ( 'TEST1','200606');
inset into TMp1 ( cname,pmonth ) values ( 'TEST1','200607');
inset into TMp1 ( cname,pmonth ) values ( 'TEST1','200608');
inset into TMp1 ( cname,pmonth ) values ( 'TEST2','200701');
inset into TMp1 ( cname,pmonth ) values ( 'TEST2','200702');
inset into TMp1 ( cname,pmonth ) values ( 'TEST2','200703');
inset into TMp1 ( cname,pmonth ) values ( 'TEST2','200704');
inset into TMp1 ( cname,pmonth ) values ( 'TEST2','200705');
inset into TMp1 ( cname,pmonth ) values ( 'TEST2','200706');
inset into TMp1 ( cname,pmonth ) values ( 'TEST2','200707');
inset into TMp1 ( cname,pmonth ) values ( 'TEST2','200708');
I want to see the lowest and the hightest pmonth for each cname along with all other data for each row.
I tried the following.
SELECT B.CNAME,B.PMONTH,
FIRST_VALUE(B.PMONTH)
OVER ( PARTITION BY B.CNAME ORDER BY B.PMONTH) FMIBP,
LAST_VALUE(B.PMONTH)
OVER ( PARTITION BY B.CNAME ORDER BY B.PMONTH ) LMIBP
FROM TMP1 B;
and I get
TEST1 200601 200601 200601
TEST1 200602 200602 200602
TEST1 200603 200603 200603
...
TEST2 200708 200708 200708
not what I expected, but if I do this
SELECT B.CNAME,B.PMONTH,
FIRST_VALUE(B.PMONTH)
OVER ( PARTITION BY B.CNAME ) FMIBP,
LAST_VALUE(B.PMONTH)
OVER ( PARTITION BY B.CNAME) LMIBP
FROM TMP1 B;
I see what I want
TEST1 200601 200601 200608
TEST1 200602 200601 200608
TEST1 200603 200601 200608
...
TEST2 200708 200701 200708
my question is why does the first query do what I expected? What is the ordery by doing?
Thanks,
Steven