group by and order shows error GROUP BY clause or be used in an aggregate function

I have a table sub_table like this:

"id"	"created_datetime"	"main_table_id"	"skill"	...	"team"
1	2023-11-01 01:00:00	1	drum	...	a
2	2023-11-02 02:00:00	1	guitar	...	a
3	2023-11-03 03:00:00	1	vocal	...	b
4	2023-11-04 04:00:00	2	bass	...	b

Goal 1:

Base on where "main_table_id"
Then group by "team" , order by "created_datetime" newest 1
Return all column

group by main_table_id , and newest 1 group by team
[{
 "id": 2, "created_datetime": 2023-11-02 02:00:00, ...
}, {
 "id": 4, "created_datetime": 2023-11-04 04:00:00, ...
}]

Goal 2:

Base on where "main_table_id" and "team"
Then group by "team" , order by "created_datetime"
Return all column

main_table_id = 1, team = a
[{
 "id": 2, "created_datetime": 2023-11-02 02:00:00, ...
}, {
 "id": 1, "created_datetime": 2023-11-01 01:00:00, ...
}]

I tried something like below query:

SELECT * FROM [table] GROUP BY team WHERE main_table_id = $1 AND team = $2

Or:

SELECT t1.* 
  FROM (
  SELECT team 
  FROM sub_table 
  GROUP BY team ORDER created_datetime DESC LIMIT 1
) t0 JOIN sub_table t1 
ON t0.team = t1.team WHERE t1.main_table_id = $1 `;

Ref: answer https://stackoverflow.com/a/19602031

Because I need to return all column, so all I tried about using GROUP BY will show error:

must appear in the GROUP BY clause or be used in an aggregate function

Any idea how to solve this?

Solution

On Postgres, we can use DISTINCT ON here:

SELECT DISTINCT ON (team) id, created_datetime, main_table_id, skill
FROM sub_table
WHERE main_table_id = $1
ORDER BY team, created_datetime DESC;