RE: How to put a TOP 1 in a select
Yes, rank return duplicates, that why I have some items with two or more line per item. >>> [EMAIL PROTECTED] 06/11/03 05:39PM >>>That's a good approach to dealing with duplicates - wrap it up in anotherquery with a group by clause to remove the duplicates. It looks like we'vegot your query down from "never" to around 30 seconds. Also since theanalytical function is doing a window sort and you will then be doinganother sort to remove duplicates the sort_area_size will have an impact onperformance - althought 30 seconds is probably good enough to not worryabout fiddling with this.Just out of curiousity - did my query also return the duplicates? To behonest, I'm not sure if RANK returns the same value for duplicates or not.I guess that's something I should look into although I barely use this typeof query.Regards, Mark. "Carol Bristow" <[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> ra.com> cc: Sent by: Subject: RE: How to put a TOP 1 in a select [EMAIL PROTECTED] .com 12/06/2003 05:25 Please respond to ORACLE-L 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 themultiple records that you want to see returned. And that will depend onthe business needs. For example, you might want the earlier date, so youcould make the top line select a.item, a.location, min(a.trans_date), b.can_vtaand add the appropriate group by. Or you may want the latest date - onlyyou can decide that.HTH,Carol BristowDPRA Inc.1300 N 17th St Suite 950Rosslyn, VA 22209Work: 703-841-8025Fax: 703-524-9415-Original Message-Sent: Wednesday, June 11, 2003 2:50 PMTo: Multiple recipients of list ORACLE-L I run the next instruction: select a.item, a.location, a.trans_date, b.can_vta from ( select item, location, trans_date, rank() over (partition by item, location order by quantity) the_rank from ictrans a 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 INNER JOIN ( select item, location, sum(quantity) as can_vta 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' group by item, location ) b on a.item=b.item and a.location=b.location and a.the_rank=1 Information: item Date Location Quantity === 0570018 5/29/2003 12:00:00 AM TJU02 -4 0570018 5/31/2003 12:00:00 AM TJU02 -3 0570018 5/30/2003 12:00:00 AM TJU02 -2 0570018 6/2/2003 12:00:00 AM TJU02 -2 0570018 6/3/2003 12:00:00 AM TJU02 -2 0570018 6/1/2003 12:00:00 AM TJU02 -1 0570018 5/30/2003 12:00:00 AM TJU24 -6 0570018 6/1/2003 12:00:00 AM TJU24 -6 0570018 6/2/2003 12:00:00 AM TJU24 -5 0570018 6/3/2003 12:00:00 AM TJU24 -3 0570018
RE: How to put a TOP 1 in a select
That's a good approach to dealing with duplicates - wrap it up in another query with a group by clause to remove the duplicates. It looks like we've got your query down from "never" to around 30 seconds. Also since the analytical function is doing a window sort and you will then be doing another sort to remove duplicates the sort_area_size will have an impact on performance - althought 30 seconds is probably good enough to not worry about fiddling with this. Just out of curiousity - did my query also return the duplicates? To be honest, I'm not sure if RANK returns the same value for duplicates or not. I guess that's something I should look into although I barely use this type of query. Regards, Mark. "Carol Bristow" <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> ra.com> cc: Sent by: Subject: RE: How to put a TOP 1 in a select [EMAIL PROTECTED] .com 12/06/2003 05:25 Please respond to ORACLE-L 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 select 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 -Original Message- Sent: Wednesday, June 11, 2003 2:50 PM To: Multiple recipients of list ORACLE-L I run the next instruction: select a.item, a.location, a.trans_date, b.can_vta from ( select item, location, trans_date, rank() over (partition by item, location order by quantity) the_rank from ictrans a 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 INNER JOIN ( select item, location, sum(quantity) as can_vta 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' group by item, location ) b on a.item=b.item and a.location=b.location and a.the_rank=1 Information: item Date Location Quantity === 0570018 5/29/2003 12:00:00 AM TJU02 -4 0570018 5/31/2003 12:00:00 AM TJU02 -3 0570018 5/30/2003 12:00:00 AM TJU02 -2 0570018 6/2/2003 12:00:00 AM TJU02 -2 0570018 6/3/2003 12:00:00 AM TJU02 -2 0570018 6/1/2003 12:00:00 AM TJU02 -1 0570018 5/30/2003 12:00:00 AM TJU24 -6 0570018 6/1/2003 12:00:00 AM TJU24 -6 0570018 6/2/2003 12:00:00 AM TJU24 -5 0570018 6/3/2003 12:00:00 AM TJU24 -3 0570018 5/31/2003 12:00:00 AM TJU24 -2 0570018 6/9/2003 12:00:00 AM TJU31 -4 Results: Item Date Location Quantity ==
RE: How to put a TOP 1 in a select
Title: Message 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 select 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 -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: select a.item, a.location, a.trans_date, b.can_vta from ( select item, location, trans_date, rank() over (partition by item, location order by quantity) the_rank from ictrans a 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 INNER JOIN ( select item, location, sum(quantity) as can_vta 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' group by item, location ) b on a.item=b.item and a.location=b.location and a.the_rank=1 Information: item Date Location Quantity === 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: Item Date Location Quantity == 05700185/29/2003 12:00:00 AM TJU02 -14 0570018 6/1/2003 12:00:00 AM TJU24 -22 0570018 5/30/2003 12:00:00 AM TJU24 -22 05700186/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 = b.itemand a.location = b.locationand b.company = 2000 and b.trans_date between (current_date-14) andcurrent_dateand b.doc_type = 'IS' and b.reason_code = 'VTCL'and substr(b.item,2,2) = '57') "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
RE: How to put a TOP 1 in a select
I run the next instruction: select a.item, a.location, a.trans_date, b.can_vta from ( select item, location, trans_date, rank() over (partition by item, location order by quantity) the_rank from ictrans a 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 INNER JOIN ( select item, location, sum(quantity) as can_vta 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' group by item, location ) b on a.item=b.item and a.location=b.location and a.the_rank=1 Information: item Date Location Quantity === 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: Item Date Location Quantity == 05700185/29/2003 12:00:00 AM TJU02 -14 0570018 6/1/2003 12:00:00 AM TJU24 -22 0570018 5/30/2003 12:00:00 AM TJU24 -22 05700186/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 = b.itemand a.location = b.locationand b.company = 2000 and b.trans_date between (current_date-14) andcurrent_dateand b.doc_type = 'IS' and b.reason_code = 'VTCL'and substr(b.item,2,2) = '57') "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
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) and current_date and b.doc_type = 'IS' and b.reason_code = 'VTCL' and substr(b.item,2,2) = '57') "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 QuantityLocation 05700045/29/2003 12:00:00 AM -1 TJU02 05700046/3/2003 12:00:00 AM -1 TJU24 05700046/9/2003 12:00:00 AM -1 TJU31 05700065/28/2003 12:00:00 AM -1 TJU24 05700106/3/2003 12:00:00 AM -1 TJU02 05700105/30/2003 12:00:00 AM -1 TJU24 05700176/3/2003 12:00:00 AM -1 TJU24 05700185/29/2003 12:00:00 AM -4 TJU02 05700185/31/2003 12:00:00 AM -3 TJU02 05700185/28/2003 12:00:00 AM -2 TJU02 05700185/30/2003 12:00:00 AM -2 TJU02 05700186/3/2003 12:00:00 AM -2 TJU02 05700186/2/2003 12:00:00 AM -2 TJU02 05700186/1/2003 12:00:00 AM -1 TJU02 05700185/30/2003 12:00:00 AM -6 TJU24 05700186/1/2003 12:00:00 AM -6 TJU24 05700186/2/2003 12:00:00 AM -5 TJU24 05700186/3/2003 12:00:00 AM -3 TJU24 05700185/31/2003 12:00:00 AM -2 TJU24 05700185/28/2003 12:00:00 AM -1 TJU24 05700186/9/2003 12:00:00 AM -4 TJU31 05700196/2/2003 12:00:00 AM -3 TJU24 05700195/28/2003 12:00:00 AM -1 TJU24 05700196/9/2003 12:00:00 AM -1 TJU31 05700206/3/2003 12:00:00 AM -2 TJU02 05700205/31/2003 12:00:00 AM -1 TJU02 05700206/2/2003 12:00:00 AM -1 TJU02 05700206/1/2003 12:00:00 AM -1 TJU24 And this should be the result Item trans_date sum( Quantity ) Location 05700045/29/2003 12:
RE: How to put a TOP 1 in a select
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 05700045/29/2003 12:00:00 AM -1 TJU0205700046/3/2003 12:00:00 AM -1 TJU2405700046/9/2003 12:00:00 AM -1 TJU3105700065/28/2003 12:00:00 AM -1 TJU2405700106/3/2003 12:00:00 AM -1 TJU0205700105/30/2003 12:00:00 AM -1 TJU2405700176/3/2003 12:00:00 AM -1 TJU2405700185/29/2003 12:00:00 AM -4 TJU0205700185/31/2003 12:00:00 AM -3 TJU0205700185/28/2003 12:00:00 AM -2 TJU0205700185/30/2003 12:00:00 AM -2 TJU0205700186/3/2003 12:00:00 AM -2 TJU0205700186/2/2003 12:00:00 AM -2 TJU0205700186/1/2003 12:00:00 AM -1 TJU0205700185/30/2003 12:00:00 AM -6 TJU2405700186/1/2003 12:00:00 AM -6 TJU2405700186/2/2003 12:00:00 AM -5 TJU2405700186/3/2003 12:00:00 AM -3 TJU2405700185/31/2003 12:00:00 AM -2 TJU2405700185/28/2003 12:00:00 AM -1 TJU2405700186/9/2003 12:00:00 AM -4 TJU3105700196/2/2003 12:00:00 AM -3 TJU2405700195/28/2003 12:00:00 AM -1 TJU2405700196/9/2003 12:00:00 AM -1 TJU3105700206/3/2003 12:00:00 AM -2 TJU0205700205/31/2003 12:00:00 AM -1 TJU0205700206/2/2003 12:00:00 AM -1 TJU0205700206/1/2003 12:00:00 AM -1 TJU24 And this should be the result Item trans_date sum( Quantity ) Location 05700045/29/2003 12:00:00 AM -1 TJU0205700046/3/2003 12:00:00 AM -1 TJU2405700046/9/2003 12:00:00 AM -1 TJU3105700065/28/2003 12:00:00 AM -1 TJU2405700106/3/2003 12:00:00 AM -1 TJU0205700105/30/2003 12:00:00 AM -1 TJU2405700176/3/2003 12:00:00 AM -1 TJU2405700185/29/2003 12:00:00 AM -16 TJU0205700185/30/2003 12:00:00 AM (or 6/1/2003 12:00:00 AM ) -23 TJU2405700186/9/2003 12:00:00 AM -4 TJU3105700196/2/2003 12:00:00 AM -4 TJU2405700196/9/2003 12:00:00 AM -1 TJU3105700206/3/2003 12:00:00 AM -4 TJU0205700206/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 WHERE a.ITEM='0570018' AND a.ITEM = b.ITEM AND a.QUANTITY = b.max_QUANTITY AND a.LOCATION=b.LOCATION AND a.TRANS_DATE BETWEEN (CURRENT_DATE-14) AND CURRENT_DATE; 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 singleICTRANS entry. If there are multiple entries per day then the logic is alot more complex - but certainly achievable. Since we don't know how datais 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
RE: How to put a TOP 1 in a select
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 ANDtrans_date BETWEEN TRUNC(SYSDATE-14) AND SYSDATE ANDdoc_type = 'IS' ANDreason_code = 'VTCL' ANDSUBSTR(item,2,2) = '57' GROUP BY item ) b WHERE a.item = b.item ANDa.qty = b.max_qty ANDtrans_date BETWEEN TRUNC(SYSDATE-14) AND SYSDATE; -Original Message- Sent: Tuesday, June 10, 2003 5:50 PM To: Multiple recipients of list ORACLE-L Thanks!! First let me explain more about the query I have a table ICTRANS that have all the inventary movements. I need to made a query that give each item from the guide 57, the information that I need is how much of each item we sell in the last two week ( from today), and what was the day that we sell more. The way I can know if it was a sell is because this condition must be true :reason_code = 'VTCL' and doc_type = 'IS'. IS means exit of inventary, that way the field Quantity have quantity with a negative sign ( ej: -30). If substr(item, 2,2) = '57' that mean that item is from the guide 57 ( patent medicine), company=2000 means that are from the same company we have one company per city. This is what I have on mind in : select ITEM , sum(QUANTITY), ( select top 1 t2.TRANS_DATE from ICTRANS t2 where t2.ITEM = t.ITEM where COMPANY = 2000 and TRANS_DATE between (sysdate-14) and sysdate and DOC_TYPE = 'IS' and REASON_CODE = 'VTCL' and substr(ITEM, 2,2) = '57' order by t2.QUANTITY desc ) from ICTRANS t where COMPANY = 2000 and TRANS_DATE between (sysdate-14) and sysdate and DOC_TYPE = 'IS' and REASON_CODE = 'VTCL' and substr(ITEM, 2,2) = '57' group by ITEM Thanks for your help I feel lost using Oracle. * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmi
RE: How to put a TOP 1 in a select
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 ANDtrans_date BETWEEN TRUNC(SYSDATE-14) AND SYSDATE ANDdoc_type = 'IS' ANDreason_code = 'VTCL' ANDSUBSTR(item,2,2) = '57' GROUP BY item ) b WHERE a.item = b.item ANDa.qty = b.max_qty ANDtrans_date BETWEEN TRUNC(SYSDATE-14) AND SYSDATE; -Original Message- Sent: Tuesday, June 10, 2003 5:50 PM To: Multiple recipients of list ORACLE-L Thanks!! First let me explain more about the query I have a table ICTRANS that have all the inventary movements. I need to made a query that give each item from the guide 57, the information that I need is how much of each item we sell in the last two week ( from today), and what was the day that we sell more. The way I can know if it was a sell is because this condition must be true :reason_code = 'VTCL' and doc_type = 'IS'. IS means exit of inventary, that way the field Quantity have quantity with a negative sign ( ej: -30). If substr(item, 2,2) = '57' that mean that item is from the guide 57 ( patent medicine), company=2000 means that are from the same company we have one company per city. This is what I have on mind in : select ITEM , sum(QUANTITY), ( select top 1 t2.TRANS_DATE from ICTRANS t2 where t2.ITEM = t.ITEM where COMPANY = 2000 and TRANS_DATE between (sysdate-14) and sysdate and DOC_TYPE = 'IS' and REASON_CODE = 'VTCL' and substr(ITEM, 2,2) = '57' order by t2.QUANTITY desc ) from ICTRANS t where COMPANY = 2000 and TRANS_DATE between (sysdate-14) and sysdate and DOC_TYPE = 'IS' and REASON_CODE = 'VTCL' and substr(ITEM, 2,2) = '57' group by ITEM Thanks for your help I feel lost using Oracle. * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chelur, Jayadas {PBSG} 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).
RE: How to put a TOP 1 in a select
use ROWNUM = 1 for instance -Original Message-From: Teresita Castro [mailto:[EMAIL PROTECTED]Sent: Tuesday, June 10, 2003 5:50 PMTo: Multiple recipients of list ORACLE-LSubject: Re: How to put a TOP 1 in a select Thanks!! First let me explain more about the query I have a table ICTRANS that have all the inventary movements. I need to made a query that give each item from the guide 57, the information that I need is how much of each item we sell in the last two week ( from today), and what was the day that we sell more. The way I can know if it was a sell is because this condition must be true :reason_code = 'VTCL' and doc_type = 'IS'. IS means exit of inventary, that way the field Quantity have quantity with a negative sign ( ej: -30). If substr(item, 2,2) = '57' that mean that item is from the guide 57 ( patent medicine), company=2000 means that are from the same company we have one company per city. This is what I have on mind in : select ITEM , sum(QUANTITY), ( select top 1 t2.TRANS_DATE from ICTRANS t2 where t2.ITEM = t.ITEM where COMPANY = 2000 and TRANS_DATE between (sysdate-14) and sysdate and DOC_TYPE = 'IS' and REASON_CODE = 'VTCL' and substr(ITEM, 2,2) = '57' order by t2.QUANTITY desc ) from ICTRANS t where COMPANY = 2000 and TRANS_DATE between (sysdate-14) and sysdate and DOC_TYPE = 'IS' and REASON_CODE = 'VTCL' and substr(ITEM, 2,2) = '57' group by ITEM Thanks for your help I feel lost using Oracle. * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. **
Re: How to put a TOP 1 in a select
Thanks!! First let me explain more about the query I have a table ICTRANS that have all the inventary movements. I need to made a query that give each item from the guide 57, the information that I need is how much of each item we sell in the last two week ( from today), and what was the day that we sell more. The way I can know if it was a sell is because this condition must be true :reason_code = 'VTCL' and doc_type = 'IS'. IS means exit of inventary, that way the field Quantity have quantity with a negative sign ( ej: -30). If substr(item, 2,2) = '57' that mean that item is from the guide 57 ( patent medicine), company=2000 means that are from the same company we have one company per city. This is what I have on mind in : select ITEM , sum(QUANTITY), ( select top 1 t2.TRANS_DATE from ICTRANS t2 where t2.ITEM = t.ITEM where COMPANY = 2000 and TRANS_DATE between (sysdate-14) and sysdate and DOC_TYPE = 'IS' and REASON_CODE = 'VTCL' and substr(ITEM, 2,2) = '57' order by t2.QUANTITY desc ) from ICTRANS t where COMPANY = 2000 and TRANS_DATE between (sysdate-14) and sysdate and DOC_TYPE = 'IS' and REASON_CODE = 'VTCL' and substr(ITEM, 2,2) = '57' group by ITEM Thanks for your help I feel lost using Oracle.
Re: How to put a TOP 1 in a select
OOPS! Mea Culpa! I improperly used rownum in the query. The previous reply from Mark is correct. However, the info on my website is correct. Daniel W. Fink http://www.optimaldba.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink 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).
Re: How to put a TOP 1 in a select
Your english is fine, lets see if I understand the query... Does it return the Item and Trans_Date of the record with the smallest quantity (ie: first row returned given that query is ordered by quantity)? Oracle (at least version 8 - not sure about 9) doesn't really support this SQL but there are workarounds: select * from (select item, trans_date where company = 2000 and trans_date between (sysdate-14) and sysdate and doc_type = 'IS' and reason_code = 'VTCL' and substr(item, 2,2) = '57' order by quantity) where rownum <= 1; This will probably return the correct result if I have understood the meaning of TOP 1 properly. Performance may or may not be an issue - although depending on how SQL Server implements this query you may not see much difference. "Teresita Castro" <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> martmx.com>cc: Sent by: Subject: How to put a TOP 1 in a select [EMAIL PROTECTED] om 10/06/2003 11:19 Please respond to ORACLE-L 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. <<>> 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).
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 (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.