"contains()" and "order by" problem.Top urgent.Thx.
713187Sep 14 2009 — edited Oct 29 2009hi,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