Select highest value per group and associated row from another table, ordered by highest value

Question

I'm trying to get these values from two tables, joining on member id. In the table with the points values, there are running total rows for each sale by each member. I need the member's point total associated with their row in the members table, sorted by value descending. This is my current query, it returns unique values unless a member has two identical entries.

SELECT m.id
     , m.fname
     , m.lname
     , p.points_total
  FROM  
     ( SELECT s.member_id
            , MAX(s.points_total) points_total 
         FROM sale_sale s
        GROUP 
           BY s.member_id
     ) p
  JOIN sale_sale x
    ON x.member_id = p.member_id 
   AND x.points_total = p.points_total
  JOIN member_member m
    ON m.id = p.member_id
 WHERE x.site_id = 1 
   AND m.fname != "Sales"
 ORDER 
    BY p.points_total DESC;

Answer 1

A simple JOIN and GROUP BY would likely do what you're asking for:

SELECT 
    m.id,
    m.fname,
    m.lname,
    COALESCE(MAX(s.points_total), 0) AS points_total 
FROM member_member AS m
LEFT JOIN sale_sale AS s
    ON m.id = s.member_id
    AND s.site_id = 1
WHERE m.fname != "Sales"
GROUP BY m.id
ORDER BY points_total DESC;

EDIT: Made it a LEFT JOIN with COALESCE(points_total, 0) to allow for members who have no sales totals to show in the results. If you don't want this, you could change LEFT JOIN to INNER JOIN and eliminate the COALESCE function.