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.