PHP MySQL Select MAX() from Group of Results

I have one MySQL query I'm struggling with and I just can't seem to make it work.

Here's what it looks like:

SELECT 
    DISTINCT reminders.recordID 
FROM 
    reminders, 
    (SELECT 
        DISTINCT recordID, 
        MAX(date) as MaxDate 
     FROM reminders 
     GROUP BY recordID
    ) dts 
WHERE 
    reminders.owner = '$owner'
    AND reminders.date = dts.MaxDate

I need to get all reminders in the table (reminders) for, say, recordID 14. Then I need to select the most recent date (MAX()) from that set of results and return the recordID with the most recent date BUT not beyond a certain date (WHERE date <= '$date').

Anybody have any ideas on how 开发者_运维技巧I might go about this?

How about ...

SELECT recordID, date
FROM reminders
WHERE owner = $owner
AND date <= $date
ORDER BY date DESC
LIMIT 1

... oh, I think I see now: "Each ID may have a couple reminders attached to it" clears something up. So there's probably a much more elegant solution to this, but I'm guessing this will work:

    SELECT DISTINCT r1.recordID,
        (SELECT r2.date
            FROM reminders as r2
            WHERE r2.owner = $owner
            AND r2.date <= $date
            AND r2.recordID = r1.recordID
            ORDER BY r2.date DESC
            LIMIT 1) as maxDate
    FROM reminders as r1
    WHERE r1.owner = $owner
    AND r1.date <= $date