with baseMin as
(
SELECT ROW_NUMBER() OVER (ORDER BY CONVERT(INT, CASE WHEN PATINDEX('%[^0-9]%', offer.Number) > 0 THEN SUBSTRING(offer.Number,0,PATINDEX('%[^0-9]%', offer.Number)) ELSE offer.Number END) desc) AS row, offer.Id as OffId, 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
INNER 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 )
,base as
(
select * from baseMin WHERE 1=1 AND row >= 1 and row < 4
)
,photosMin as
(
select p.OfferId, p.[Order] from POS_Photo p
)
,photosFirst as
(
select p.OfferId, p.[FileName] from POS_Photo p
inner join (SELECT OfferId, MIN([Order]) AS Ord FROM photosMin GROUP BY OfferId) AS pMin
ON p.OfferId=pMin.OfferId AND p.[Order]=pMin.Ord
)
SELECT tbl.*, offer.*, prop.*, 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 base AS tbl
INNER JOIN POS_Offer offer ON tbl.OffId=offer.Id
INNER JOIN IMO_Property prop ON tbl.OffId=prop.OfferId
LEFT OUTER JOIN POS_Location country ON prop.CountryId = country.Id
LEFT OUTER JOIN POS_Location province ON prop.ProvinceId = province.Id
LEFT OUTER JOIN POS_Location region ON prop.RegionId = region.Id
LEFT OUTER JOIN POS_DictValue material ON prop.BuildMaterialId = material.Id
LEFT OUTER JOIN POS_DictValue standard ON prop.StandardId = standard.Id
LEFT OUTER JOIN POS_Description dsc ON tbl.OffId=dsc.OfferId
LEFT OUTER JOIN photosFirst pht ON tbl.OffId=pht.OfferId
ORDER BY row
