Geek Answers Handbook

Order in a group?

I have clients on my system. Customers have programs. I want to display a list of clients showing their latest active (if one exists) program.

So 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 that the program is not closed. Thus, first unlocked programs will be opened, and then the most recent closed programs.

The problem is that the order does not work within groups. He simply chooses one of the programs at random. How to 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 the correct results. Is that right, or was I just lucky? those. I essentially sorted the table before joining it; these results will be sorted since they join, right?

Solution: I now consider this a classic group max problem. Find this if you encounter a similar problem. The solution involves joining the same table twice.

+7
source share
2 answers
 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 switch 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 entries equal to zero, close_date='9999-12-31' , then your ORDER BY will be simpler (and the entire query is faster with the corresponding indexes):

  ORDER BY pi.close_date DESC
+7
source

Try this order on offer ...

 ORDER BY client.id, CASE WHEN program.close_date = 0 THEN 0 ELSE 1 END, program.close_date DESC
+1
source

More articles:


All Articles