SELECT TD_ASC.PROVINCE_NM,
TD_ASC.CITY_NM,
TD_ASC.FMC_REGION_NM,
TD_ASC.ASC_CODE,
TD_ASC.ASC_NAME,
TD_VIN.MODEL_TYPE,
SUM(TF_ASC_BO_PART.PART_QTY),
SUM(TF_ASC_BO_PART.PART_SALE_AMT)
FROM TD_ASC,
TD_VIN,
TF_ASC_BO_PART,
TD_DATE,
TD_ASC_PART
WHERE (TD_DATE.DATE_ID = TF_ASC_BO_PART.BALANCE_DT_ID)
AND (TF_ASC_BO_PART.ASC_CODE = TD_ASC.ASC_CODE)
AND (TD_ASC_PART.PART_ID = TF_ASC_BO_PART.ASC_BO_PART_ID)
AND (TF_ASC_BO_PART.VIN = TD_VIN.VIN)
AND ((TD_DATE.CALENDAR_DATE BETWEEN '2006-10-01'
AND '2006-10-31')
AND TD_ASC_PART.SGM_PART_FLG = 'Y'
AND TD_VIN.MODEL_TYPE IN (SELECT VIEW__1.COLUMN__1
FROM (SELECT TD_VIN.MODEL_TYPE AS COLUMN__1,
RANK() OVER(ORDER BY SUM(TF_ASC_BO_PART.PART_SALE_AMT) DESC) AS RK__1
FROM TD_VIN,
TF_ASC_BO_PART
WHERE (TF_ASC_BO_PART.VIN = TD_VIN.VIN)
GROUP BY TD_VIN.MODEL_TYPE) VIEW__1
WHERE VIEW__1.RK__1 <= 10))
GROUP BY TD_ASC.PROVINCE_NM,TD_ASC.CITY_NM,TD_ASC.FMC_REGION_NM,
TD_ASC.ASC_CODE,TD_ASC.ASC_NAME,TD_VIN.MODEL_TYPE