Select latest record

Databases
1

Hi,

Below query shows me all the records in between two dates which is fine but i want to select the records group by property_manager1 and listing_status so that listing_status should not be repeated for a property_manager1:

SELECT (SELECT project FROM master_project WHERE id=p.property_master_project) AS property_master_project, (SELECT project FROM project WHERE id=p.property_project) AS property_project, (SELECT employee_code FROM users WHERE id=p.property_manager1) AS property_manager1, DATE_FORMAT(o.dates,‘%d/%m/%Y’) as dates, o.statuss as listing_status FROM plot p INNER JOIN operations o ON(p.market_appraisal_ref=o.mar_ref) WHERE (p.listing_status=‘Listed’ OR p.listing_status=‘Unlisted’) AND (o.statuss=‘Listed’ OR o.statuss=‘Vendor Phone Call’ OR o.statuss=‘Price Increase’ OR o.statuss=‘Price Reduction’) AND o.dates>=‘2014-08-01’ AND o.dates<=‘2014-08-21’ order by o.dates desc

2

please elaborate on this, your question is unclear

also, p.s., i don’t know how anybody can work with sql that’s all strung out on one horrendously long single line

so i reformatted your query for you –

SELECT ( SELECT project 
           FROM master_project 
          WHERE id = p.property_master_project ) AS property_master_project
     , ( SELECT project 
           FROM project 
          WHERE id = p.property_project ) AS property_project
     , ( SELECT employee_code 
           FROM users 
          WHERE id = p.property_manager1 ) AS property_manager1
     , DATE_FORMAT(o.dates,'%d/%m/%Y') AS dates
     , o.statuss AS listing_status 
  FROM plot p 
INNER 
  JOIN operations o 
    ON o.mar_ref = p.market_appraisal_ref 
   AND o.statuss IN ('Listed'
                    ,'Vendor Phone Call'
                    ,'Price Increase'
                    ,'Price Reduction') 
   AND o.dates >= '2014-08-01' 
   AND o.dates <= '2014-08-21' 
 WHERE p.listing_status IN ('Listed'
                           ,'Unlisted') 
ORDER 
    BY o.dates DESC
1 Like