| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to put a TOP 1 in a select
There may be different ways to write this query - otherwise you need to look at tuning this query. Four minutes for a single product in a 5 million row table doesn't sound really good but I guess it depends on hardware.
Have a look at the explain plan for the query - you have a lot of selection criteria on the ICTRANS table. If one (or a group) of those criteria is very restrictive then index (and analyze) that column(s) to see the performance gain.
I've had a go at writing this as an analytical query. The syntax may be incorrect since I don't have anything convenient to test it against. There may 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_rank
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') a,
ictrans b
where a.the_rank = 1
and a.item = b.item and a.location = b.location and b.company = 2000 and b.trans_date between (current_date-14) andcurrent_date
"Teresita Castro"
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
martmx.com> cc:
Sent by: Subject: RE: How to put a TOP 1 in a
select
[EMAIL PROTECTED]
om
11/06/2003 11:59
Please respond to
ORACLE-L
This is an example of the information.
I forgot to mention that in each company we have supermarkets called in the
system Locations. So when I do this query I have to return per item the
total of sales in the las two week, the day that we sale more per
supermarket
Item trans_date Quantity Location
0570004 5/29/2003 12:00:00 AM -1 TJU02 0570004 6/3/2003 12:00:00 AM -1 TJU24 0570004 6/9/2003 12:00:00 AM -1 TJU31 0570006 5/28/2003 12:00:00 AM -1 TJU24 0570010 6/3/2003 12:00:00 AM -1 TJU02 0570010 5/30/2003 12:00:00 AM -1 TJU24 0570017 6/3/2003 12:00:00 AM -1 TJU24 0570018 5/29/2003 12:00:00 AM -4 TJU02 0570018 5/31/2003 12:00:00 AM -3 TJU02 0570018 5/28/2003 12:00:00 AM -2 TJU02 0570018 5/30/2003 12:00:00 AM -2 TJU02 0570018 6/3/2003 12:00:00 AM -2 TJU02 0570018 6/2/2003 12:00:00 AM -2 TJU02 0570018 6/1/2003 12:00:00 AM -1 TJU02 0570018 5/30/2003 12:00:00 AM -6 TJU24 0570018 6/1/2003 12:00:00 AM -6 TJU24 0570018 6/2/2003 12:00:00 AM -5 TJU24 0570018 6/3/2003 12:00:00 AM -3 TJU24 0570018 5/31/2003 12:00:00 AM -2 TJU24 0570018 5/28/2003 12:00:00 AM -1 TJU24 0570018 6/9/2003 12:00:00 AM -4 TJU31 0570019 6/2/2003 12:00:00 AM -3 TJU24 0570019 5/28/2003 12:00:00 AM -1 TJU24 0570019 6/9/2003 12:00:00 AM -1 TJU31 0570020 6/3/2003 12:00:00 AM -2 TJU02 0570020 5/31/2003 12:00:00 AM -1 TJU02 0570020 6/2/2003 12:00:00 AM -1 TJU02 0570020 6/1/2003 12:00:00 AM -1 TJU24And this should be the result
Item trans_date sum( Quantity ) Location
0570004 5/29/2003 12:00:00 AM -1 TJU02 0570004 6/3/2003 12:00:00 AM -1 TJU24 0570004 6/9/2003 12:00:00 AM -1 TJU31 0570006 5/28/2003 12:00:00 AM -1 TJU24 0570010 6/3/2003 12:00:00 AM -1 TJU02 0570010 5/30/2003 12:00:00 AM -1 TJU24 0570017 6/3/2003 12:00:00 AM -1 TJU24 0570018 5/29/2003 12:00:00 AM -16 TJU02 0570018 5/30/2003 12:00:00 AM (or 6/1/2003 12:00:00 AM ) -23 TJU24 0570018 6/9/2003 12:00:00 AM -4 TJU31 0570019 6/2/2003 12:00:00 AM -4 TJU24 0570019 6/9/2003 12:00:00 AM -1 TJU31 0570020 6/3/2003 12:00:00 AM -4 TJU02 0570020 6/1/2003 12:00:00 AM -1 TJU24
I have a question I run this query:
SELECT distinct a.ITEM, a.TRANS_DATE,a.LOCATION,
b.tot_QUANTITY
FROM ICTRANS a,
(
SELECT ITEM, LOCATION,SUM(QUANTITY) tot_QUANTITY, MAX(QUANTITY)
max_QUANTITY
FROM ICTRANS WHERE COMPANY = 2000
-- and LOCATION='TJU02'
AND TRANS_DATE BETWEEN (CURRENT_DATE-14) AND CURRENT_DATE
AND DOC_TYPE = 'IS' AND REASON_CODE = 'VTCL'
AND SUBSTR(ITEM,2,2) = '57'
GROUP BY ITEM ,LOCATION
) b
Just for 1 item and afther 4:12 minutes I have the results, the table ICTRANS have 4,628,226 rows, that is normal? When I tried to run the instruccion with out the item='0570018' it never ends.
>>> [EMAIL PROTECTED] 06/10/03 05:29PM >>>
Your query returns the maximum quantity (and associated date) for a single
ICTRANS entry. If there are multiple entries per day then the logic is a
lot more complex - but certainly achievable. Since we don't know how data
is stored in the table though the query below MAY be valid.
"Chelur, Jayadas
{PBSG}" To: Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc:
epsi.com> Subject: RE: How to put a
TOP 1 in a select
Sent by:
[EMAIL PROTECTED]
.com
11/06/2003 04:25
Please respond to
ORACLE-L
This query would give you the total quantity sold in the past two weeks and the date on which maximum number was sold, for each item ...
SELECT a.item,
a.tras_date AS max_sale_date,
b.tot_qty AS tot_sale_qty
FROM ICTRANS a,
(
SELECT item, SUM(qty) tot_qty, MAX(qty) max_qty
FROM ICTRANS
WHERE company = 2000
AND trans_date BETWEEN TRUNC(SYSDATE-14) AND SYSDATE
AND doc_type = 'IS'
AND reason_code = 'VTCL'
AND SUBSTR(item,2,2) = '57'
GROUP BY item
) b
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Jun 10 2003 - 21:50:04 CDT
 |
 |