Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Analytic question

40342Apr 5 2006 — edited Apr 14 2006
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 12 2006
Added on Apr 5 2006
8 comments
1,219 views