mysql select record containing highest value, joining on range of columns containing nulls
===

Here's what I'm working with:

    CREATE TABLE IF NOT EXISTS `rate` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `client_company` int(11) DEFAULT NULL,
      `client_group` int(11) DEFAULT NULL,
      `client_contact` int(11) DEFAULT NULL,
      `role` int(11) DEFAULT NULL,
      `date_from` datetime DEFAULT NULL,
      `hourly_rate` decimal(18,2) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    
    INSERT INTO `rate` (`id`, `client_company`, `client_group`, `client_contact`, `role`, `date_from`, `hourly_rate`) VALUES
    (4, NULL, NULL, NULL, 3, '2012-07-30 14:48:16', 115.00),
    (5, 3, NULL, NULL, 3, '2012-07-30 14:51:38', 110.00),
    (6, 3, NULL, NULL, 3, '2012-07-30 14:59:20', 112.00);

This table stores chargeout rates for clients; the idea being that, when looking for the correct rate for a job role, we'd first look for a rate matching the given role and client contact, then if no rate was found, would try to match the role and the client group (or 'department'), then the client company, and finally looking for a global rate for just the role itself. Fine.

Rates can change over time, so the table may contain multiple entries matching any given combination of role, company, group and client contact: I want a query that will only return me the latest one for each distinct combination.

Given that [I asked a near-identical question only days ago][1], and that this topic seems fairly frequent in various guises, I can only apologise for my slow-wittedness and ask once again for someone to explain why the query below is returning all three of the records above and not, as I want it to, only the records with IDs 4 and 6.

Is it something to do with my trying to join based on columns containing NULL?

    SELECT
    	rate.*,
    	newest.id
    FROM rate
    	LEFT JOIN rate AS newest ON(
    		rate.client_company = newest.client_company
    		AND rate.client_contact = newest.client_contact
    		AND rate.client_group = newest.client_group
    		AND rate.role= newest.role
    		AND newest.date_from > rate.date_from
    	)
    WHERE newest.id IS NULL

  [1]: http://stackoverflow.com/questions/11614966/mysql-select-distinct-values-against-a-range-of-columns