SELECT TOP 200 L.LatestID, L.LatestHeading, L.LatestDesc, L.LatestDatePosted, L.LatestOrgType, L.LatestOrgID, CASE WHEN (L.LatestNationActive='Y') THEN 1 ELSE 99 END AS Priority, CASE WHEN L.LatestOrgType='E' THEN ENT.EntityName WHEN L.LatestOrgType='C' THEN C.CityDesc WHEN L.LatestOrgType='T' THEN T.CountyDesc WHEN L.LatestOrgType='S' THEN S.StateDesc WHEN L.LatestOrgType='R' THEN R.RegionDesc WHEN L.LatestOrgType='N' THEN NAT.NationDesc END AS OrgName FROM Latests L LEFT JOIN Entitys ENT ON L.LatestOrgType='E' AND L.LatestOrgID=ENT.EntityID LEFT JOIN Citys C ON L.LatestOrgType='C' AND L.LatestOrgID=C.CityID LEFT JOIN Countys T ON L.LatestOrgType='T' AND L.LatestOrgID=T.CountyID LEFT JOIN States S ON L.LatestOrgType='S' AND L.LatestOrgID=S.StateID LEFT JOIN Regions R ON L.LatestOrgType='R' AND L.LatestOrgID=R.RegionID LEFT JOIN Nations NAT ON L.LatestOrgType='N' AND L.LatestOrgID=NAT.NationID WHERE L.LatestActive='Y' AND L.LatestNationActive='Y' AND LatestTrainingActive = 'Y' ORDER BY Priority ASC, L.LatestOrgType ASC, L.LatestOrgID ASC, L.LatestID DESC
