Hi TOAll,

i used the FF SQL statement.

SELECT     LTRIM(RTRIM(SALESTABLE.SALESID)) AS ORDERNO, LTRIM(RTRIM(CUSTINVOICEJOUR.CUSTOMERREF)) as ESANO, CUSTINVOICEJOUR.DLVMODE, LTRIM(RTRIM(CUSTINVOICEJOUR.ORDERACCOUNT))AS ORDERACCOUNT, CUSTTABLE.NAME,   
                      CUSTCONFIRMJOUR.CONFIRMDOCNUM, CUSTCONFIRMJOUR.CONFIRMDATE,CUSTINVOICETRANS.ITEMID, INVENTTABLE.ITEMNAME,   
                      CUSTINVOICEJOUR.CURRENCYCODE, CUSTINVOICEJOUR.EXCHRATE/100 AS EXRATE, (INVENTTABLE.TARAWEIGHT + CUSTINVOICETRANS.QTY) AS GROSSWEIGHT,
                      CUSTINVOICETRANS.QTY, CUSTINVOICETRANS.SALESPRICE, LTRIM(RTRIM(CUSTINVOICETRANS.INVOICEID))AS INVOICEID, CUSTINVOICETRANS.INVOICEDATE,   
                      INVENTTABLE.GIS_OFASUBSTRATE, INVENTTABLE.GIS_OFABRAND, INVENTDIM.INVENTLOCATIONID AS WMSLOCATIONID,INVENTDIM.INVENTSIZEID,  
                      CASE WHEN LTRIM(RTRIM(INVENTDIM.INVENTSIZEID)) =  '2E' THEN CUSTINVOICETRANS.QTY / 32.4  
                           WHEN LTRIM(RTRIM(INVENTDIM.INVENTSIZEID)) =  '1E' THEN CUSTINVOICETRANS.QTY / 226.80  
                         ELSE CUSTINVOICETRANS.QTY END AS QTYINCARTOONS, SALESTABLE.PURCHORDERFORMNUM AS CUSTOMERREF, CUSTINVOICETRANS.DLVDATE,   
                      DATEPART(week,CUSTINVOICEJOUR.INVOICEDATE) as NoofWeek, ForeCastSales.inventqty as Forecast,  
                      PriceDiscTable.Amount as FOB, InventTable.GIS_OFASubStrate as Fiber_type  
  
FROM         CUSTINVOICEJOUR,SALESTABLE,CUSTINVOICETRANS,INVENTTABLE,CUSTTABLE,CUSTCONFIRMJOUR, INVENTDIM,  PriceDiscTable, ForeCastSales  
                      WHERE  
                      CUSTINVOICEJOUR.SALESID = SALESTABLE.SALESID AND 
                      CUSTINVOICEJOUR.DATAAREAID = SALESTABLE.DATAAREAID AND  
                      LTRIM(RTRIM(CUSTINVOICEJOUR.DIMENSION)) = '1' AND                  
                      CUSTINVOICEJOUR.SALESID = CUSTCONFIRMJOUR.SALESID AND  
                      CUSTINVOICEJOUR.ORDERACCOUNT = CUSTTABLE.ACCOUNTNUM AND  
                      CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID AND   
                      CUSTINVOICEJOUR.INVOICEDATE = CUSTINVOICETRANS.INVOICEDATE AND 
                      INVENTTABLE.ITEMID = CUSTINVOICETRANS.ITEMID AND  
                      INVENTDIM.INVENTDIMID = CUSTINVOICETRANS.INVENTDIMID AND  
                      CUSTINVOICEJOUR.INVOICEDATE >= @FROM_DATE AND 
                      CUSTINVOICEJOUR.INVOICEDATE <= @TO_DATE AND  
                      ltrim(rtrim(INVENTTABLE.ITEMID))*=Ltrim(Rtrim(PRICEDISCTABLE.ITEMRELATION)) AND  
                      PRICEDISCTABLE.ACCOUNTCODE = 2 AND  
                      ltrim(rtrim(CUSTINVOICEJOUR.ORDERACCOUNT)) *= LTRIM(RTRIM(ForeCastSales.CustAccountID))  AND  
                      ltrim(rtrim(CUSTINVOICETRANS.ITEMID)) *= LTRIM(RTRIM(ForeCastSales.ITEMID))  AND  
                      Month(ForeCastSales.StartDate) =Month(@to_date) and Year(ForeCastSales.StartDate) = Year(@To_Date)  ORDER BY ORDERNO,CUSTCONFIRMJOUR.CONFIRMDOCNUM

Output..

orderno	          confirmdocnum	itemno
SO017008	          SO017008-1	SSPA04923NR51R
SO017008	          SO017008-1	SSPA02909NR51R
SO017008	          SO017008-1	SSPA02752NR51R
SO017008	          SO017008-1	SSPA02754NR51R
SO017008	          SO017008-2	SSPA02752NR51R
SO017008	          SO017008-2	SSPA02909NR51R
SO017008	          SO017008-2	SSPA04923NR51R
SO017008	          SO017008-2	SSPA02754NR51R
SO018318	          SO018318-1	SSPA02904NR51R
SO018318	          SO018318-1	SSPA04923NR51R
SO018318	          SO018318-1	SSPA02754NR51R
SO018318	          SO018318-1	SSPA29033NR51R
SO018318	          SO018318-2	SSPA29033NR51R
SO018318	          SO018318-2	SSPA02754NR51R
SO018318	          SO018318-2	SSPA04923NR51R
SO018318	          SO018318-2	SSPA02904NR51R
SO018318	          SO018318-3	SSPA02904NR51R
SO018318	          SO018318-3	SSPA04923NR51R
SO018318	          SO018318-3	SSPA02754NR51R
SO018318	          SO018318-3	SSPA29033NR51R

How can i get the max confirmdocnum? what commands do i need to add to my SQL statement to get this output? pls modify my SQL Statement to produce this output.

This is the correct output. 

orderno	         confirmdocnum	itemno
SO017008	          SO017008-2	SSPA02752NR51R
SO017008	          SO017008-2	SSPA02909NR51R
SO017008	          SO017008-2	SSPA04923NR51R
SO017008	          SO017008-2	SSPA02754NR51R
SO018318	          SO018318-3	SSPA02904NR51R
SO018318	          SO018318-3	SSPA04923NR51R
SO018318	          SO018318-3	SSPA02754NR51R
SO018318	          SO018318-3	SSPA29033NR51R

Thanks In Advance.