SELECT tbl.*, country.Name as Country, province.Name as Province, region.Name as Region, material.Value as BuildMaterial,standard.Value as Standard,pht.FileName AS MainPhoto,COALESCE(District, City) AS DistrictCity,COALESCE(Commune, Section) AS CommuneSection,dsc.Description AS Description FROM (
SELECT * FROM(
SELECT ROW_NUMBER() OVER (ORDER BY Area Desc) AS row, offer.*, offer.Id as OffId, prop.*, buildType.Value as BuildingType,district.Name as District, city.Name as City, commune.Name as Commune, section.Name as Section, street.Name as Street
FROM POS_Offer offer
JOIN IMO_Property prop ON prop.OfferId = offer.Id
LEFT OUTER JOIN POS_Location district ON prop.DistrictId = district.Id
LEFT OUTER JOIN POS_Location city ON prop.CityId = city.Id
LEFT OUTER JOIN POS_Location commune ON prop.CommuneId = commune.Id
LEFT OUTER JOIN POS_Location section ON prop.SectionId = section.Id
LEFT OUTER JOIN POS_Street street ON prop.StreetId = street.Id
LEFT OUTER JOIN POS_DictValue buildType ON prop.BuildingTypeId = buildType.Id WHERE offer.Deleted=0 AND offer.LanguageId=0 AND (TypeId = 0)) AS rows WHERE 1=1 AND row >= 61 and row < 71
) AS tbl
LEFT OUTER JOIN POS_Location country ON tbl.CountryId = country.Id
LEFT OUTER JOIN POS_Location province ON tbl.ProvinceId = province.Id
LEFT OUTER JOIN POS_Location region ON tbl.RegionId = region.Id
LEFT OUTER JOIN POS_DictValue material ON tbl.BuildMaterialId = material.Id
LEFT OUTER JOIN POS_DictValue standard ON tbl.StandardId = standard.Id
LEFT OUTER JOIN POS_Description dsc ON tbl.OffId=dsc.OfferId
LEFT OUTER JOIN (select p2.* from (select OfferId, MIN([Order]) as m from POS_Photo group by OfferId) as p1 inner join POS_Photo p2 on p1.OfferId=p2.OfferId and p1.m=p2.[Order]) as pht ON tbl.OffId=pht.OfferId
ORDER BY row