--replace this:
--LEFT JOIN LM001_property_listing pl ON pl.propertyID = p.propertyID 

--with this
LEFT JOIN (	--derived table of most recent listing_start records by propertyid
		Select	propertyid, max(listing_start) listing_start
		From	LM001_property_listing a
		JOIN	LM001_property b
				ON a.propertyID = b.PropertyID
		Where	p.userid = 300001
		Group by propertyid
		)  pl ON pl.propertyID = p.propertyID 

--replace this:
--LEFT JOIN LM001_property_listing pl ON pl.propertyID = p.propertyID 

--with this
LEFT JOIN (	--derived table of most recent listing_start records by propertyid
		Select	top 1 listing_start, listing_end
		From	LM001_property_listing a
		Where	propertyID = p.PropertyID
		order by listing_start desc
		)  pl ON pl.propertyID = p.propertyID 

SELECT p.propertyID, p.title, p.address1, p.address2, p.town, p.postcode, p.price, ppt.price_term, pt.property_term, pty.property_type, lt.listing_type, ss.sale_status, pl.listing_start as start, 
CASE
WHEN pl.listing_end IS NULL AND pl.listing_start IS NOT NULL THEN 'NEVER'
WHEN pl.listing_end < GETDATE() AND pl.listing_end IS NOT NULL THEN 'EXPIRED'
WHEN pl.listing_end IS NULL AND pl.listing_start IS NULL THEN 'UNPAID'
ELSE CONVERT(varchar(36), pl.listing_end, 9)
END AS 'expires'
FROM LM001_property p 
LEFT JOIN LM001_property_price_term ppt ON ppt.price_termID = p.price_termID
LEFT JOIN LM001_property_term pt ON pt.property_termID = p.property_termID
LEFT JOIN LM001_property_type pty ON pty.property_typeID = p.property_typeID
LEFT JOIN LM001_listing_type lt ON lt.listing_typeID = p.listing_typeID
LEFT JOIN LM001_sale_status ss ON ss.sale_statusID = p.sale_statusID
LEFT JOIN LM001_property_listing pl 
JOIN 
(
	SELECT 
		pl2.propertyID, 
		MAX(pl2.listing_start) AS listing_start 
	FROM 
		dbo.LM001_property_listing pl2
	GROUP BY 
		pl2.propertyID
) AS pl2
ON pl.propertyID = pl2.propertyID
AND pl.listing_start = pl2.listing_start
ON pl.propertyID = p.propertyID 

WHERE p.userID = 300001
ORDER BY lt.listing_type

--with this
LEFT JOIN (	--derived table of most recent listing records by propertyid
		Select	a.PropertyID, a.Listing_start, max(a.Listing_end) listing_end
		From	From	LM001_property_listing a
		JOIN	(--another derived table to get the latest listing_start for each propertyid
			Select	PropertyID, max(listing_start) listing_start
			From	LM001_property_listing
			group by propertyID
			) b
				ON a.propertyID = b.PropertyID
				and a.listing_start = b.listing_start
		Group by a.PropertyID, a.Listing_start
		)  pl ON pl.propertyID = p.propertyID