How can I select the last N in a nested subquery join?

Question

I'm familiar with finding the last item from a child group or table (for example), but I am now trying to do the same with a second layer, using a nested join.

Consider the following schema and data:

/*
SQLyog Ultimate v11.52 (64 bit)
MySQL - 5.6.17 : Database - test
*********************************************************************
*/

/*Table structure for table `companies` */

CREATE TABLE `companies` (
  `company_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

/*Data for the table `companies` */

insert  into `companies`(`company_id`,`company_name`) values (1,'Company1'),(2,'Company2');

/*Table structure for table `employees` */

CREATE TABLE `employees` (
  `employee_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `employee_name` varchar(16) NOT NULL,
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

/*Data for the table `employees` */

insert  into `employees`(`employee_id`,`company_id`,`employee_name`) values (1,1,'Employee1'),(2,1,'Employee2'),(3,2,'Employee3'),(4,2,'Employee4');

/*Table structure for table `orders` */

CREATE TABLE `orders` (
  `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int(10) unsigned NOT NULL,
  `order_date` datetime NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

/*Data for the table `orders` */

insert  into `orders`(`order_id`,`employee_id`,`order_date`) values (1,1,'2016-02-01 00:00:00'),(2,1,'2016-02-02 00:00:00'),(3,2,'2016-02-03 00:00:00'),(4,2,'2016-01-28 00:00:00'),(5,3,'2016-01-29 00:00:00'),(6,3,'2016-01-30 00:00:00'),(7,4,'2016-01-31 00:00:00'),(8,4,'2016-01-27 00:00:00');

Answering the question "What is the last order from each employee?" is a relatively straightforward query:

SELECT c.company_name, e.employee_name, last_order.order_id, last_order.order_date
FROM employees e
JOIN companies c ON c.company_id = e.company_id
LEFT JOIN
(
    SELECT o1.employee_id, o1.order_id, o1.order_date
    FROM orders o1
    LEFT JOIN orders o2 ON (o1.employee_id = o2.employee_id AND o1.order_date < o2.order_date)
    WHERE o2.order_id IS NULL
) AS last_order ON last_order.employee_id = e.employee_id;

-- output ===================================================

company_name  employee_name  order_id  order_date           
Company1      Employee1      2         2016-02-02 00:00:00  
Company1      Employee2      3         2016-02-03 00:00:00  
Company2      Employee3      6         2016-01-30 00:00:00  
Company2      Employee4      7         2016-01-31 00:00:00   

But now I need to ask "What is the last order from each company?" where the orders are another layer deep (companies > employees > orders).

I have been trying to figure out how to construct a query that will allow me to join employees to employees using the already determined "last order" in order to do the same sort of comparison:

 -- pseudo code...
 SELECT *
 FROM employees e1
 LEFT JOIN employees e2 ON (e1.company_id = e2.company_id AND e1.last_order_date < e2.last_order_date)
 WHERE e2.employee_id IS NULL;

Something like that, but since "last order date" is a construct of the first query, how do I get such a result to appear on each employee table for the join?

Do I need to have lots of nested queries to accomplish this sort of thing? Would a temporary table be a better solution? Is there something simple I am missing?

I would like to end up with just the last order per company for this result:

-- desired output ============================================

company_name  employee_name  order_id  order_date           
Company1      Employee1      1         2016-02-01 00:00:00  
Company2      Employee3      5         2016-01-29 00:00:00  

Edit:

The ">" in the first query should have been a "<" - I've modified this as well as the output to show what was intended: the last orders rather than the first. Apologies for that; this will eventually be used to do both, and I forgot to change the logic for the example.

Answer 1

EDIT

Check the edit history if you want to see the old answer that provided results that were seemingly ok but were actually wrong.

The following query will pull multiple rows if a company has multiple orders with the exact same order_date. (there's a more robust solution below)

SELECT
    o.order_date
    , o.order_id
    , e.employee_id
    , c.company_name
    , e.employee_name
FROM (
    SELECT
        MAX(o.order_date) AS order_date, e.company_id
    FROM orders o
    INNER JOIN employees e ON o.employee_id = e.employee_id
    GROUP BY e.company_id
) maxOrderDate
INNER JOIN orders o ON maxOrderDate.order_date = o.order_date
INNER JOIN employees e ON o.employee_id = e.employee_id AND o.employee_id = e.employee_id AND maxOrderDate.company_id = e.company_id
INNER JOIN companies c ON e.company_id = c.company_id

The following query accounts for that pesky multiple-orders-with-the-exact-same-order_date situation

SELECT
    o.order_date
    , o.order_id
    , e.employee_id
    , c.company_name
    , e.employee_name
FROM (
    SELECT MAX(o.order_id) AS order_id
    FROM (
        SELECT
            MAX(o.order_date) AS order_date, e.company_id
        FROM orders o
        INNER JOIN employees e ON o.employee_id = e.employee_id
        GROUP BY e.company_id
    ) maxOrderDate
    INNER JOIN orders o ON maxOrderDate.order_date = o.order_date
    INNER JOIN employees e ON o.employee_id = e.employee_id AND o.employee_id = e.employee_id AND maxOrderDate.company_id = e.company_id
    GROUP BY e.company_id
) maxOrder
INNER JOIN orders o ON maxOrder.order_id = o.order_id
INNER JOIN employees e ON o.employee_id = e.employee_id
INNER JOIN companies c ON e.company_id = c.company_id