SELECT DISTINCT MM.MODEL_NAME,GMM.DESCRIPTION, T.CATEGORY_NAME, MM.CATEGORY_ID, MAX(MM.MODEL_ID) MODEL_ID FROM T_GPS_MODELS MM, T_CATEGORIES T,T_GPS_MASTER_MODELS GMM WHERE MM.CATEGORY_ID IN (SELECT DISTINCT T_CATEGORIES.CATEGORY_ID --, T_CATEGORIES.CATEGORY_NAME FROM T_CATEGORIES T_CATEGORIES, T_CHILD_CATEGORIES T_CHILD_CATEGORIES WHERE T_CATEGORIES.CATEGORY_ID = T_CHILD_CATEGORIES.CHILD_CATEGORY_ID START WITH T_CHILD_CATEGORIES.CATEGORY_ID IN (SELECT CATEGORY_ID FROM T_CATEGORIES WHERE CATEGORY_NAME IN (SELECT T_GPS_APP_PROPERTIES.PROPERTY_VALUE FROM T_GPS_APPLICATIONS, T_GPS_APP_PROPERTIES Where Application_Name Like 'Business Process Mapping' AND T_GPS_APP_PROPERTIES.APPLICATION_ID = T_GPS_APPLICATIONS.APPLICATION_ID AND T_GPS_APP_PROPERTIES.PROPERTY_NAME LIKE 'CATEGORY%')) CONNECT BY PRIOR T_CHILD_CATEGORIES.CHILD_CATEGORY_ID = T_CHILD_CATEGORIES.CATEGORY_ID UNION SELECT C.CATEGORY_ID --,C.CATEGORY_NAME From T_Categories C, T_Gps_Applications A, T_Gps_App_Properties Ap WHERE A.APPLICATION_NAME LIKE 'Business Process Mapping' AND A.APPLICATION_ID = AP.APPLICATION_ID AND AP.PROPERTY_NAME LIKE 'CATEGORY%' AND C.CATEGORY_NAME = AP.PROPERTY_VALUE) AND MM.CATEGORY_ID = T.CATEGORY_ID AND MM.MODEL_ID = GMM.MODEL_ID AND MM.MODEL_NAME NOT LIKE 'Scenario %' AND MM.MODEL_NAME NOT LIKE '%$%' Group By Mm.Model_Name, T.Category_Name, Mm.Category_Id,GMM.DESCRIPTION Order By Max(Mm.Model_Id) Desc