How to solve "SELECT list is not in GROUP BY clause and contains nonaggregated column" wit-CodePudding

table:

id	action	project_id	created_on	last_updated_on
1	Update	123	2021.1.1	2021.5.3
2	creation	123	2021.1.4	2021.5.2
3	Update	123	2021.1.3	2021.5.1
4	Update	456	2021.2.1	2021.6.3
5	Update	456	2021.2.2	2021.6.2
6	creation	456	2021.2.3	2021.6.1

I would like to get a map of project and its most recently created Update action's last_updated_on.

My statement is

select project_id, last_updated_on 
from table
where action = "Update"
group by project_id
order by created_on desc
limit 1

But I got the error of SELECT list is not in GROUP BY clause and contains nonaggregated column.

I cannot change the ONLY_FULL_GROUP_BY atrribute.

And in my use case I think I cannot add last_updated_on into the group by either.

I also tried

select 
    project_id, 
    (select last_updated_on where created_on = max(created_on)) as "last_updated_on"
    .
    .
    .

but the error still occurred.

Hope someone can help me. Tnanks in advance!

Update:

Desired result of the example table:

project_id	last_updated_on
123	2021.5.1
456	2021.6.2

Explanation:

For project_id = 123, row 1 and 3's action are update,
after order by created_on desc limit 1, row 3 is selected, whose last_updated_on is 2021.5.1.

CodePudding user response：

You can do:

select project_id, last_updated_on
from (
  select *, row_number() over(partition by project_id 
                              order by last_updated_on desc) as rn
  from t
  where action = 'Update'
) x
where rn = 1
order by created_on

Will show the last Update action for each project. Projects with no Update action at all won't show up.