mpen mpen - 2 years ago 74
SQL Question

Order within group by?

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:

FROM clients AS client
JOIN programs AS program ON
ORDER BY program.close_date=0 DESC, program.close_date DESC

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:

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

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 Source
SELECT  c.*, p.*
FROM    clients AS c
JOIN    programs AS p
ON = 
        FROM    programs AS pi
        WHERE   pi.client_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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download