SELECT aaa.Name, aaa.CompanyName, aaa.State, aaa.Deep
FROM (SELECT b.Name AS Name, c.Company_Name AS CompanyName, c.State AS State,
a.Deep AS Deep
FROM Contract a LEFT OUTER JOIN
Floor_Office b ON a.Floor_Office_ID = b.ID LEFT OUTER JOIN
Company c ON a.Lessee = c.Company_Name
WHERE (b.Build_ID = '1') AND (b.Property_ID = '1') AND (a.DeleteFlg = 1) AND
(b.DeleteFlg = 1) AND (c.DeleteFlg = 1) AND (c.If_Cn = 1)) aaa,
(SELECT Name, MAX(Deep) AS Deep
FROM (SELECT b.Name AS Name, c.Company_Name AS CompanyName,
c.State AS State, a.Deep AS Deep
FROM Contract a LEFT OUTER JOIN
Floor_Office b ON a.Floor_Office_ID = b.ID LEFT OUTER JOIN
Company c ON a.Lessee = c.Company_Name
WHERE (b.Build_ID = '1') AND (b.Property_ID = '1') AND (a.DeleteFlg = 1)
AND (b.DeleteFlg = 1) AND (c.DeleteFlg = 1) AND (c.If_Cn = 1))
bbb
GROUP BY bbb.name) ccc
WHERE aaa.Name = ccc.name AND aaa.Deep = ccc.Deep
ORDER BY aaa.CompanyName