Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to put a TOP 1 in a select

RE: How to put a TOP 1 in a select

From: Carol Bristow <Carol.Bristow_at_dpra.com>
Date: Wed, 11 Jun 2003 11:42:14 -0700
Message-ID: <F001.005AF685.20030611112543@fatcity.com>

That's always going to happen with something like a rank (or min/max).  You'll need to add some additional criteria to tell Oracle which of the multiple records that you want to see returned.  And that will depend on the business needs.  For example, you might want the earlier date, so you could make the top line <FONT face="Courier New" color=#0000ff
POINT-SIZE="9">    select<FONT face="Courier New" color=#000000 POINT-SIZE="9"> a.item, a.location, min(a.trans_date), b.can_vta
and add the appropriate group
by.  Or you may want the latest date - only you can decide that.
 
HTH,
 

Carol Bristow
DPRA Inc.
1300 N 17th St Suite 950
Rosslyn, VA 22209
Work: 703-841-8025
Fax: 703-524-9415<FONT
color=#000080 size=2>
 

-----Original Message-----From: Teresita Castro [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 2:50 PMTo: Multiple recipients of list ORACLE-LSubject: RE: How to put a TOP 1 in a select

  I run the next instruction:
      

   
<FONT face="Courier New" color=#0000ff
  POINT-SIZE="9">select<FONT face="Courier New" color=#000000   POINT-SIZE="9"> a.item, a.location, a.trans_date, b.can_vta
<FONT

  face="Courier New" color=#0000ff POINT-SIZE="9">from<FONT   face="Courier New" color=#000000 POINT-SIZE="9"> (
<FONT

  face="Courier New" color=#0000ff POINT-SIZE="9">select<FONT   face="Courier New" color=#000000 POINT-SIZE="9"> item, location,   trans_date,
<FONT face="Courier New" color=#000000
  POINT-SIZE="9">       rank() over (<FONT

  face="Courier New" color=#0000ff POINT-SIZE="9">partition<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"> item, location <FONT 
  face="Courier New" color=#0000ff 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)

<FONT face="Courier New" color=#000000
  POINT-SIZE="9">the_rank
<FONT

  face="Courier New" color=#0000ff POINT-SIZE="9">from<FONT   face="Courier New" color=#000000 POINT-SIZE="9"> ictrans a
<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) 
  and<FONT
  face="Courier New" color=#000000 POINT-SIZE="9">
<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">) a   INNER JOIN      (
<FONT
  face="Courier New" color=#0000ff POINT-SIZE="9">select<FONT 
  face="Courier New" color=#000000 POINT-SIZE="9"> item, location, <FONT 
  face="Courier New" color=#0000ff POINT-SIZE="9">sum<FONT 
  face="Courier New" color=#000000 POINT-SIZE="9">(quantity) <FONT 
  face="Courier New" color=#0000ff POINT-SIZE="9">as<FONT 
  face="Courier New" color=#000000 POINT-SIZE="9"> can_vta

<FONT

  face="Courier New" color=#0000ff POINT-SIZE="9">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)   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">
<FONT
  face="Courier New" color=#0000ff POINT-SIZE="9">group<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"> item, location
  ) b
<FONT
  face="Courier New" color=#0000ff POINT-SIZE="9">on<FONT 
  face="Courier New" color=#000000 POINT-SIZE="9"> a.item=b.item <FONT 
  face="Courier New" color=#0000ff POINT-SIZE="9">and<FONT 
  face="Courier New" color=#000000 POINT-SIZE="9"> a.location=b.location 
  and<FONT
  face="Courier New" color=#000000 POINT-SIZE="9"> a.the_rank=1    
  Information:
<FONT

  face="Courier New">item         
  Date     Location Quantity
<FONT
  face="Courier New">=======================================
  0570018  5/29/2003 12:00:00 
  AM TJU02 -40570018  5/31/2003 12:00:00 
  AM TJU02 -30570018  5/30/2003 12:00:00   AM TJU02 -20570018  6/2/2003 12:00:00 AM    TJU02 -20570018  6/3/2003 12:00:00
  AM  TJU02 -20570018  6/1/2003 12:00:00 AM    TJU02 -10570018  5/30/2003
  12:00:00 AM TJU24 -60570018  6/1/2003 12:00:00
  AM  TJU24 -60570018  6/2/2003 12:00:00 
  AM  TJU24 -50570018  6/3/2003 12:00:00 AM 
   TJU24 -30570018  5/31/2003 12:00:00 

  AM TJU24 -20570018  6/9/2003 12:00:00 AM    TJU31 -4
   
  Results:   

<FONT

  face="Courier New">Item         
  Date     Location Quantity
<FONT

  face="Courier New">==========================================
  0570018    5/29/2003 12:00:00 AM  
  TJU02  -14   <FONT
  color=#0000ff>0570018   6/1/2003 12:00:00 AM  TJU24 
  -22   0570018  5/30/2003 12:00:00 AM TJU24  
  -22   0570018    6/9/2003 
  12:00:00 AM     TJU31   -4   
  

   
   
  The problem is that when we have an item that   sold two or more days the same quantity and it is the   biggest quantity it returns more that one row per item.   This query is execute in 28 seconds
   
   
  >>> [EMAIL PROTECTED] 06/10/03 09:24PM   >>>There may be different ways to write this query  -   otherwise you need tolook at tuning this query.  Four minutes for a   single product in a 5million row table doesn't sound really good but I   guess it depends onhardware.Have a look at the explain plan for   the query - you have a lot of selectioncriteria on the ICTRANS   table.  If one (or a group) of those criteria isvery restrictive then   index (and analyze) that column(s) to see theperformance gain.I've   had a go at writing this as an analytical query.  The syntax may   beincorrect since I don't have anything convenient to test it   against.  Theremay even be syntax errors - it was simply typed in   without being executed.Good luck!!!select a.item, a.location,   a.trans_date, sum(b.quantity) from (select item, location,   trans_date       rank() over (partition by   item, location order by quantity desc)the_rankfrom ictranswhere   company = 2000 and trans_date between (current_date-14)   andcurrent_dateand doc_type = 'IS' and reason_code = 'VTCL'and   substr(item,2,2) = '57') a,ictrans bwhere a.the_rank = 1and a.item

Received on Wed Jun 11 2003 - 13:42:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US