Select not groupable id on a group by query

Question

I am using Mysql 8.0 and trying to execute the following query, but I am not achieving the desired result.I need to get the max updated_at grouped by companies.

The table:

id  | updated_at          | company
-----------------------------------
5   | 2011-04-14 01:06:06 | 1
3   | 2011-04-14 01:05:41 | 2
7   | 2011-04-15 01:14:14 | 2

The query:

select id, MAX(updated_at), company
from signatures
group by company

I am having an error because id could not be in a group.

Could someone help me with a query that can do the job, please?

Thanks in advance

Answer 1

Use window functions:

select distinct
  first_value(id) over (partition by company order by updated_at desc) id,
  max(updated_at) over (partition by company) updated_at,
  company
from signatures