Re: Help:about rownum

From: sun newsgroup <billgcheng_at_21cn.com>
Date: Fri, 20 Oct 2000 14:10:33 +0800
Message-ID: <8sq841$2cr$1@news.cz.js.cn>

Hello,
I think you want to fetch the first 3 smallest records on column a. It is referred to as "Top N query". ROWNUM means that order that Oracle fetches the record. Even if you use 'ORDER BY' in SQL, the ROWNUM associated with a specific will not change. The correct SQL is:
SELECT * FROM
   (select a,b,c from table1,table2,table3where table1.a1=table2.b1 and table3=table1.c1and order by a) t
WHERE ROWNUM < 4 ;
 㽭 <peter_at_aps.com.cn> wrote in message news:8s3ntn$1gao$1_at_news.cz.js.cn...
  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?

--

Received on Fri Oct 20 2000 - 01:10:33 CDT