Re: Help:about rownum

In article <39e72ced.13945382_at_news.news-ituk.to>,   See Message body for real address wrote:
>
> As long as you are cross-posting, try searching deja...this is one of
 the most
> often asked questions...order by happens after the 3 rows are
 retrieved; the 3
> rows are in no particular order...
>
> Try instead
>
> select * from (select a,b,c,from table1,table2,table3 where
 table1.a1=table2.b1
> and table3=table1.c1order by a) where rownum < 4;
>
> Õã½­ <peter_at_aps.com.cn> wrote:
>
> >Help me:
> >When I use rownum:
> >example:
> >select a,b,c
> >from table1,table2,table3
> >where table1.a1=table2.b1 and table3=table1.c1
> >order by a
> >
> >result:
> >a b c
> >1 ad ds
> >2 ds sd
> >21 sad dd
> >3 lds kd
> >4 sd sddf
> >
> >now :use rownum
> >select a,b,c
> >from table1,table2,table3
> >where table1.a1=table2.b1 and table3=table1.c1
> >and rownum<4 order by a
> >result:
> >a b c
> >1 ad ds
> >2 ds sd
> >4 sd sddf
> >Why???????? how can I get the correct result?
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 80,000 Newsgroups - 16 Different Servers! =-----
>

I believe, Turk, you have not understood the nature of the problem. Apparently column A from table1 is a VARCHAR field, not a NUMBER field, and, as such, uses the ASCII collating sequence which places '21' ahead of '3'. You might want to try the following:

select a,b,c
from table1,table2,table3
where table1.a1=table2.b1 and table3=table1.c1 order by to_number(a)

This should produce the desired result.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.