Re: How to put a TOP 1 in a select

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 <font

 point-size="9">(COMPANY = 2000) <font
 color="#000000">
 <font

 point-size="9">       AND (TRANS_DATE <font
 point-size="9">BETWEEN  (CURRENT_DATE-14)  <font
 point-size="9">AND CURRENT_DATE)
  

        <font

 point-size="9">AND (DOC_TYPE = <font
 point-size="9">'IS')  

        <font

 point-size="9">AND (REASON_CODE = <font  point-size="9">'VTCL')  

        <font

 point-size="9">AND (SUBSTR(ITEM, 2, 2) = <font  point-size="9">'57')  

      order<font
 point-size="9"> by<font
 point-size="9"> 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:
<blockquote type="cite"
 cite="">  
  
 
  
 
  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<font
 face="Courier New" color="#000000" point-size="9"> TOP 1 ITEM, TRANS_DATE
  from<font
 face="Courier New" color="#000000" point-size="9"> ICTRANS <font
 face="Courier New" color="#0000ff" point-size="9">where<font
 face="Courier New" color="#000000" point-size="9">  
    (COMPANY
=  2000) 
 
   <font
 face="Courier New" color="#0000ff" point-size="9">AND<font
 face="Courier New" color="#000000" point-size="9"> (TRANS_DATE <font
 face="Courier New" color="#0000ff" point-size="9">BETWEEN<font
 face="Courier New" color="#000000" point-size="9">  (CURRENT_DATE-14)  <font
 face="Courier New" color="#0000ff" point-size="9">AND<font
 face="Courier New" color="#000000" point-size="9"> CURRENT_DATE)
 
   
 
   <font
 face="Courier New" color="#0000ff" point-size="9">AND<font
 face="Courier New" color="#000000" point-size="9"> (DOC_TYPE = <font
 face="Courier New" color="#ff0000" point-size="9">'IS'<font
 face="Courier New" color="#000000" point-size="9">) 
 
   <font
 face="Courier New" color="#0000ff" point-size="9">AND<font
 face="Courier New" color="#000000" point-size="9"> (REASON_CODE = <font
 face="Courier New" color="#ff0000" point-size="9">'VTCL'<font
 face="Courier New" color="#000000" point-size="9">) 
 
   <font
 face="Courier New" color="#0000ff" point-size="9">AND<font
 face="Courier New" color="#000000" point-size="9"> (SUBSTR(ITEM, 2, 2) =
 '57'<font
 face="Courier New" color="#000000" point-size="9">)
 
  order<font
 face="Courier New" color="#000000" point-size="9"> <font
 face="Courier New" color="#0000ff" point-size="9">by<font
 face="Courier New" color="#000000" point-size="9"> QUANTITY
  
 
  I hope you undestand my  English.