CODEWITHSUNDEEP
mysql
Tyilo
Tyilo

Reputation: 30102

Remove duplicates from one column keeping whole rows

id | userid | total_points_spent
1  | 1      | 10
2  | 2      | 15
3  | 2      | 50
4  | 3      | 5
5  | 1      | 15

With the above table, I would first like to remove duplicates of userid keeping the rows with the largest total_points_spent, like so:

id | userid | total_points_spent
3  | 2      | 50
4  | 3      | 5
5  | 1      | 15

And then I would like to sum the values of total_points_spent, which would be the easy part, resulting in 70.

Upvotes: 1

Views: 116

Answers (1)

sel
sel

Reputation: 4957

I am not really sure the "remove" you meant is to delete or to select. Here is the query for select only max totalpointspend record respectively.

SELECT tblA.* 
  FROM ( SELECT userid, MAX(totalpointspend) AS maxtotal
           FROM tblA
           GROUP BY userid ) AS dt
INNER JOIN tblA 
    ON tblA.userid = dt.userid
   AND tblA.totalpointspend = dt.maxtotal           
ORDER BY tblA.userid

Upvotes: 2

Related Questions