SELECT TOP 200 L.LatestID, L.LatestHeading, L.LatestDesc, L.LatestDatePosted, L.LatestOrgType, L.LatestOrgID, CASE WHEN (L.LatestStateActive='Y' AND SO.SOStateID IS NOT NULL) THEN 1 WHEN (L.LatestRegionActive='Y' AND RO.ROOrgID IS NOT NULL) THEN 2 WHEN (L.LatestNationActive='Y' AND NAO.NOOrgID IS NOT NULL) THEN 3 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 LEFT JOIN StateOrgs SO ON SO.SOOrgType=L.LatestOrgType AND SO.SOOrgID=L.LatestOrgID AND SO.SOStateID=22 LEFT JOIN RegionOrgs RO ON RO.ROOrgType=L.LatestOrgType AND RO.ROOrgID=L.LatestOrgID AND RO.RORegionID=10 LEFT JOIN NationOrgs NAO ON NAO.NOOrgType=L.LatestOrgType AND NAO.NOOrgID=L.LatestOrgID AND NAO.NONationID=3 WHERE L.LatestActive='Y' AND LatestITActive = 'Y' ORDER BY Priority ASC, L.LatestOrgType ASC, L.LatestOrgID ASC, L.LatestID DESC
