Re: Help:about rownum
Date: Thu, 12 Oct 2000 22:56:11 GMT
Message-ID: <8s5fi7$aqi$1_at_nnrp1.deja.com>
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.Received on Fri Oct 13 2000 - 00:56:11 CEST