title: SQL join: counting rows and get most recent row from another table
body: <p>Okay, so I've already got a fairly complex SQL statement to get my information, but I need to add something else to it.</p>
  
  <p>Basically, the items get a count of the files and also a count of the types of files associated with them (amongst other things)...</p>
  
  <p>I need to also get the ID of the latest file for floorplan and presentation AS another separate field</p>
  
  <pre><code>SELECT 
    items.*,
    designers.name AS designer,
    locations.name AS location,
    COUNT(DISTINCT files.id) AS files,
    SUM(files.type = 'floorplan') AS floorplans,
    SUM(files.type = 'presentation') AS presentations,
    SUM(files.type = 'photo') AS photos 
  FROM
    items 
    LEFT JOIN designers 
      ON items.designer_id = designers.id 
    JOIN locations 
      ON items.location_id = locations.id 
    LEFT JOIN files 
      ON (items.id = files.item_id) 
  WHERE items.location_id = 8 
  GROUP BY items.id 
  </code></pre>
  
  <p>I have looked at other QAs about joining the most recent row of another table, but they are based on having no joins to begin with and with my existing query I have no idea where to even begin. I still need the file counts and I can't join a table twice.</p>
  
  <p>So to summarise, I need all that as well as the IDs for the most recent floorplan and most recent presentation. Files have a date_uploaded column containing a unix timestamp.</p>