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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

"contains()" and "order by" problem.Top urgent.Thx.

713187Sep 14 2009 — edited Oct 29 2009
hi,I have two sql the only difference between them is just the " ORDER BY score(1) desc ,ORDERNO asc ".But to my surprise, the second can not work well. As follows.
Anyone could give me some advice?

==================the first one is==========================
SELECT *
FROM (
SELECT row_.*, rownum rownum_
FROM (
SELECT c.*
FROM node c,S_title s1
WHERE EXISTS (
SELECT y.PKID FROM NODE_TYPE y WHERE y.PKID=s1.NODE_ID AND y.PKID=c.PKID
)
and contains(s1.value,'keywords',1)>0
) row_
WHERE rownum <= 5
)
WHERE rownum_ > 0
---------------------------------------------------------------------
result: (right result)
1 2460159331308 1000000000000 2460006163686 35513 1
2 2460159331306 1000000000000 2460006163686 35511 2
3 2460159331298 1000000000000 2460006163686 35503 3
4 2485611253409 1000000000000 37967 4
5 2485611253407 1000000000000 37965 5


==================the second one is======================
SELECT *
FROM (
SELECT row_.*, rownum rownum_
FROM (
SELECT c.*
FROM node c,S_title s1
WHERE EXISTS (
SELECT y.PKID FROM NODE_TYPE y WHERE y.PKID=s1.NODE_ID AND y.PKID=c.PKID
)
and contains(s1.value,'keywords',1)>0 ORDER BY score(1) desc ,ORDERNO asc -----------------the only difference between them
) row_
WHERE rownum <= 20
)
WHERE rownum_ > 0
--------------------------------------------------
result: (wrong result)
1 2460159331308 1000000000000 2460006163686 35513 1
2 2460159331308 1000000000000 2460006163686 35513 2
3 2460159331308 1000000000000 2460006163686 35513 3
4 2460159331308 1000000000000 2460006163686 35513 4
5 2460159331308 1000000000000 2460006163686 35513 5
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 26 2009
Added on Sep 14 2009
18 comments
1,089 views