SELECT p.id AS id, p.mls_id, p.pool, p.price, p.call_for_price, p.show_map, p.hide_address, p.price2, p.surface_area_terrace, p.stype_freq, p.title AS title, p.beds, p.baths, p.sqft, p.lot_acres, p.lotsize, p.street_num, p.street, p.street2, p.province, p.apt, p.golf_close, p.sea_view, p.description as description, p.short_description, p.created, p.created_by, p.modified, p.stype, p.listing_office, p.city, p.locstate, p.county, p.region, p.subdivision, p.country, p.province, p.alias as alias, p.featured, p.latitude, p.longitude, p.available, p.income, p.tax, p.publish_up, p.state, p.checked_out, p.checked_out_time, p.hits, p.listing_info, pm.cat_id
FROM psi48_iproperty AS p
LEFT JOIN psi48_iproperty_propmid as pm ON pm.prop_id = p.id
WHERE pm.cat_id > 0 AND p.approved = 1 AND pm.cat_id IN (56,68,69,70)
GROUP BY p.id
ORDER BY p.created DESC
SELECT p.id AS id, p.mls_id, p.pool, p.price, p.call_for_price, p.show_map, p.hide_address, p.price2, p.surface_area_terrace, p.stype_freq, p.title AS title, p.beds, p.baths, p.sqft, p.lot_acres, p.lotsize, p.street_num, p.street, p.street2, p.province, p.apt, p.golf_close, p.sea_view, p.description as description, p.short_description, p.created, p.created_by, p.modified, p.stype, p.listing_office, p.city, p.locstate, p.county, p.region, p.subdivision, p.country, p.province, p.alias as alias, p.featured, p.latitude, p.longitude, p.available, p.income, p.tax, p.publish_up, p.state, p.checked_out, p.checked_out_time, p.hits, p.listing_info, pm.cat_id
FROM psi48_iproperty AS p
LEFT JOIN psi48_iproperty_propmid as pm ON pm.prop_id = p.id
WHERE pm.cat_id > 0 AND `p`.`featured` IN ('1') AND p.approved = 1 AND pm.cat_id IN (56,68,69,70)
GROUP BY p.id
ORDER BY RAND()
SELECT p.id AS id, p.mls_id, p.pool, p.price, p.call_for_price, p.show_map, p.hide_address, p.price2, p.surface_area_terrace, p.stype_freq, p.title AS title, p.beds, p.baths, p.sqft, p.lot_acres, p.lotsize, p.street_num, p.street, p.street2, p.province, p.apt, p.golf_close, p.sea_view, p.description as description, p.short_description, p.created, p.created_by, p.modified, p.stype, p.listing_office, p.city, p.locstate, p.county, p.region, p.subdivision, p.country, p.province, p.alias as alias, p.featured, p.latitude, p.longitude, p.available, p.income, p.tax, p.publish_up, p.state, p.checked_out, p.checked_out_time, p.hits, p.listing_info, pm.cat_id
FROM psi48_iproperty AS p
LEFT JOIN psi48_iproperty_propmid as pm ON pm.prop_id = p.id
WHERE pm.cat_id > 0 AND p.approved = 1 AND pm.cat_id IN (56,68,69,70)
GROUP BY p.id
ORDER BY p.created DESC
Sorry, no records were found. Please try again.
Current Featured Properties
€620,000
Townhouse in Istan
- Istán, Costa Del Sol, Istan
Beds: 3 Baths: 3 M2: 197 Area: Istan Region: Andalucía
Beds: 3 Baths: 3 M2: 197 Area: Istan Region: Andalucía
STUNNING MOUNTAIN AND SEA VIEWS!!!
This new development encompasses precisely this combination of the best of the mountains and coast on Costa del Sol. Originally the location of an ancient oil mill (...)
€3,650,000
TSEA4450162
- Estepona, Costa Del Sol, Estepona
Beds: 3 Baths: 4 M2: 364 Lot Size: 106 Area: Estepona Region: Andalucía
Beds: 3 Baths: 4 M2: 364 Lot Size: 106 Area: Estepona Region: Andalucía
Discover coastal living in this exquisite townhome, ideally situated with views of the serene ocean waves . Boasting breathtaking views of both sunrises and sunsets, this home is a true masterpiece (...)
€950,000
TSEA4430956
- Estepona, Costa Del Sol, Estepona
Beds: 5 Baths: 5 M2: 240 Area: Estepona Region: Andalucía
Beds: 5 Baths: 5 M2: 240 Area: Estepona Region: Andalucía
Exceptional very spacious townhouse located in frontline beach residential complex Belgravia club, Estepona.
This spectacular home has fantastic sea views and the location is unbeatable, just a few (...)
