Help with a query -- limit one row per match

[arenaninja]

I'm using MySQL 5.5.21. I have a table, the show create statement as follows:

| feeschedule | CREATE TABLE `feeschedule` (
  `pkRateID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fkEquipID` int(10) unsigned NOT NULL,
  `fkInstTypeID` int(10) unsigned NOT NULL,
  `rate` decimal(6,2) NOT NULL,
  `dateOfEffect` date NOT NULL,
  PRIMARY KEY (`pkRateID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 |

This table records rates for a particular Equipment (fkEquipID) for different institution types (fkInstTypeID). As these are subject to change, the changes are distinguished by dateOfEffect. However, I'm having issues filtering properly. I would like to select ONLY the data matching the most recent unique fkEquipID and fkInstTypeID entry.

I tried:

 SELECT * FROM feeschedule WHERE dateOfEffect<='2012-07-27' GROUP BY fkEquipID,fkInstTypeID ORDER BY dateOfEffect DESC;

but though this does return only one column per matching fkEquipID and fkInstTypeID, it is not the most recent one.

 

Your help is greatly appreciated.

[Barand]

SELECT f.fkEquipID, f.fkInstTypeID, f.rate, f.dateOfEffect
FROM feeschedule f
INNER JOIN (
    SELECT fkEquipID, fkInstTypeID, MAX(dateOfEffect) as latest
    FROM feeschedule
    GROUP BY fkEquipID, fkInstTypeID
) as x
ON f.fkEquipID = x.fkEquipID AND f.fkInstTypeID = x.fkInstTypeID AND f.dateOfEffect = x.latest