mysql - Order within group by?

Question

In my system, I have clients. Clients have programs. I want to display a list of clients, showing their most recent active (if it exists) program.

Thus, we have something like this:

SELECT * 
FROM clients AS client 
    JOIN programs AS program ON client.id=program.client_id
GROUP BY client.id
ORDER BY program.close_date=0 DESC, program.close_date DESC

close_date=0 means the program isn't closed. So it will put the non-closed programs first, and then the most recently closed programs next.

Problem is, the order by doesn't work within the groups. It just kind of picks one of the programs at random. How do I resolve this?

---

Just came up with this:

SELECT * 
FROM clients AS client 
    JOIN (SELECT * FROM programs AS program ORDER BY program.close_date=0 DESC, program.close_date DESC) AS program ON client.id=program.client_id
GROUP BY client.id

Which seems to give correct results. Is this correct, or am I just getting lucky? i.e., I've essentially sorted the table before joining on it; those results will stay sorted as it does the join, right?

---

Solution: I now believe this a classic group-wise maximum problem. Search for that if you're stuck on a similar problem. The solution involves joining the same table twice.

Answer 1

SELECT  c.*, p.*
FROM    clients AS c
JOIN    programs AS p
ON      p.id = 
        (
        SELECT  pi.id
        FROM    programs AS pi
        WHERE   pi.client_id = c.id
        ORDER BY
                pi.close_date=0 DESC, pi.close_date DESC
        LIMIT 1
        )

Thanx should go to @Quassnoi. See his answer in a similar (but more complicated) question: mysql-group-by-to-display-latest-result

---

If you update the programs table and set close_date for all records that it is zero to close_date='9999-12-31', then your ORDER BY will be simpler (and the whole query faster with proper indexes):

        ORDER BY
                pi.close_date DESC