Filtering out most recent row in a join query

Question

I have 2 tables with the following information. Unfortunately my basic knowledge of JOINS & MySQL isn't helping me achieve the result set I need... point 3 in particular is eluding me. Any help is greatly appreciated.

ORDERS

OrderID     OrderDate   Client      Country 

68349       1/1/2018    GSC Ltd     US
93909       1/1/2018    XYZ Ltd     UK
39900       3/31/2018   ABC Ltd     US
69382       4/4/2018    ABC Ltd     US
94500       6/21/2018   DEF Ltd     US
93911       1/1/2018    TUV Ltd     UK

REPORTS

RepID   State   RepDate     Building    OrderID

1   AK  1/1/2018    PE James    93909
2   CA  4/2/2018    PE Grid     93909
3   CA  3/31/2018   PMU Jaya    39900
4   WA  4/4/2018    PMU Taman   69382
5   CA  6/21/2018   PE Grid     94500
6   CA  3/31/2018   PMU Wate    39900

I need to select all based on :

1. Select orders where the Country is = US
2. Select reports that were a part of those US Orders (ie Order ID = Order ID from result above)
3. Most importantly: Filter the result of the reports so that it only shows the single latest Report (based on most Recent RepDate) for each Building

So the results would be:

OrderID     OrderDate   Client      Country     RepID   RepDate     Building 

68349       1/1/2018    GSC Ltd     US      -   -       -
39900       3/31/2018   ABC Ltd     US      3   3/31/2018   PMU Jaya
69382       4/4/2018    ABC Ltd     US      4   4/4/2018    PMU Taman
94500       6/21/2018   DEF Ltd     US      5   6/21/2018   PE Grid

The result below would be excluded since there is a more recent report for "PE Grid":

OrderID     OrderDate   Client      Country     RepID   RepDate     Building 

39900       3/31/2018   ABC Ltd     US      6   3/31/2018   PE Grid

Answer 1

To find the latest report for each building is the trick here. First, you need to find the date of the latest report, like this.

               SELECT MAX(RepDate) RepDate, Building
                 FROM reports
                GROUP BY RepDate

GROUP BY guarantees you'll have only one row here per building.

Then you need to treat that query as a virtual table and join to it. (http://sqlfiddle.com/#!9/ade437e/2/0)

SELECT o.OrderID, o.OrderDate, o.Client, o.Country,
       r.RepId, r.RepDate, r.Building
  FROM orders o
  LEFT JOIN reports r ON o.OrderID = r.OrderID
  LEFT JOIN ( SELECT MAX(RepDate) RepDate, Building
                FROM reports
                GROUP BY RepDate
            ) m ON r.RepDate = m.RepDate AND r.Building = m.Building

Notice the use here of LEFT JOIN. That means orders with no matching report will still show up in the result set. If you don't want those orders, change LEFT JOIN to JOIN.

If you want to filter that result set add WHERE o.Country = 'US' (or the filter of your choice) to the end of the query. Then you can use ORDER BY to sort the result set if that's what you need.

The trick is to build the query from the inside out.

By the way, this is called the greatest n per group pattern.