Ordering a MySQL query with joins and groups

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?

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

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