Re: How to put a TOP 1 in a select

[Daniel W. Fink]

You are right, Oracle does not have the functionality built in. You can use an inline view to accomplish the same thing.

select ictrans1.item, ictrans1.trans_date
from (select rownum i_rownum,
             ITEM,
             TRANS_DATE
      from ICTRANS
      where (COMPANY = 2000)

        AND (TRANS_DATE BETWEEN  (CURRENT_DATE-14) AND CURRENT_DATE)

        AND (DOC_TYPE = 'IS')

        AND (REASON_CODE = 'VTCL')

        AND (SUBSTR(ITEM, 2, 2) = '57')

      order by QUANTITY) ictrans1
where itrans1.i_rownum = 1

I have a paper/presentation that discusses the Top-N type queries at www.optimaldba.com/library.html.
-- 
Daniel W. Fink
http://www.optimaldba.com

Teresita Castro wrote:

Hi!!

I was working with SQL Server 2000, and now the company decided to change to Oracle. So I don't know much about Oracle, I am just reading a book and try to find information on the net.

I am trying to do the next query, but I guess Oracle did not have the function TOP. How can I do a TOP 1 in Oracle?

 

select TOP 1 ITEM, TRANS_DATE from ICTRANS where

  (COMPANY = 2000)

 AND (TRANS_DATE BETWEEN  (CURRENT_DATE-14) AND CURRENT_DATE)

 

 AND (DOC_TYPE = 'IS')

 AND (REASON_CODE = 'VTCL')

 AND (SUBSTR(ITEM, 2, 2) = '57')

order by QUANTITY

 

I hope you undestand my English.