Ordering a MySQL query with joins and groups

Question

I have this MySql query:

SELECT *
FROM Customer c 
JOIN eHRDemographic ehD ON ehD.CxID = c.CustomerID  
JOIN CustPrimaryWeight cpW ON cpW.CxID = c.CustomerID  
WHERE c.CustomerID =22703 
GROUP BY c.CustomerID 
ORDER BY cpW.CustPrimaryWeightID DESC

This doesn't really work correctly as the CustPrimaryWeight table has multiple entries and it's simply joining the first entry and not the more recent one as the ORDER statement doesn't seem to do anything.

Any ideas?

Answer 1

Would http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html help you?

Answer 2

I'm not quite sure what you're trying to do. If you want to sort the CustPrimaryWeight table before the join you can try something like this.

SELECT *
FROM Customer c 
JOIN eHRDemographic ehD ON ehD.CxID = c.CustomerID  
JOIN (SELECT * FROM CustPrimaryWeight ORDER BY CustPrimaryWeightID DESC) cpW ON cpW.CxID = c.CustomerID  
WHERE c.CustomerID =22703 
GROUP BY c.CustomerID 

But since you're grouping by CustomerID, I think you are trying to show the maximum CustPrimaryWeight data for each customer.

SELECT *
FROM Customer c 
JOIN eHRDemographic ehD ON ehD.CxID = c.CustomerID  
JOIN (SELECT * FROM CustPrimaryWeight 
      WHERE CustPrimaryWeightID = (SELECT MAX(CustPrimaryWeightID)
                                   FROM CustPrimaryWeight
                                   WHERE CustomerID = c.CustomerID)
      ) cpW ON cpW.CxID = c.CustomerID  
WHERE c.CustomerID =22703 
GROUP BY c.CustomerID