Re: How to put a TOP 1 in a select

[Teresita Castro]

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.