Query is returns the wrong value for row

Question

I have the following query.

SELECT MAX(activity_id), group_id, parent_group_id FROM dashboard_activity_detail WHERE account_id = 8997 AND parent_group_id IN (5118,5026,4522,3983,3586,3278,3227) AND activity_type_id = 18 GROUP BY parent_group_id;

My expectation is that the group_id will be returned associated with the largest activity_id. There are multiple rows with the same parent_group_id in the table. What I get back is a value from a different row. The activity_id is correct and the parent_group_id is correct but I get a value from a different row for the group_id.

What am I missing? I've tried order by and various other methods with the same result.

score 0 · Accepted Answer · answered Aug 04 '20 at 00:26

You expectation is wrong. You query is malformed because the SELECT columns are inconsistent with the GROUP BY columns.

Use window functions (available in MySQL 8+):

SELECT da.*
FROM (SELECT da.*,
             ROW_NUMBER() OVER (PARTITION BY parent_group_id ORDER BY activity_id DESC) as seqnum
      FROM dashboard_activity_detail da
      WHERE account_id = 8997 AND
            parent_group_id IN (5118,5026,4522,3983,3586,3278,3227) AND
            activity_type_id = 18 
     ) da
WHERE seqnum = 1;

Query is returns the wrong value for row

1 Answers1