I want to select mutiple columns in group by query

Question

this is my TEAM table

POSITION NAME      HEIGHT
________ _________ ______
GK       Jhon      178
GK       Steven    190
DF       Paul      183   
DF       Andrew    178
DF       Nick      169
MF       Ali       170
MF       Peter     176
MF       Charlie   180
FW       Simon     185
FW       Son       184
FW       Jack      179

I want to select position, name and highest players each positions like:

POSITION NAME      HEIGHT
________ _________ ______
GK       Steven    190
DF       Paul      183
MF       Charlie   180
FW       Simon     185

Here is the code I tried.

SELECT POSITION, MAX(HEIGHT) FROM TEAM GROUP BY POSITION; 

and that's the answer:

POSITION HEIGHT
________ ______
GK       190
DF       183
MF       180
FW       185  

As a result, I hope to insert name column on that result.

Answer 1

You can add a keep/last column expression:

MAX(NAME) KEEP (DENSE_RANK LAST ORDER BY HEIGHT)

Giving:

SELECT POSITION,
  MAX(NAME) KEEP (DENSE_RANK LAST ORDER BY HEIGHT) AS NAME,
  MAX(HEIGHT) AS HEIGHT
FROM TEAM
GROUP BY POSITION; 

POSITION	NAME	HEIGHT
DF	Paul	183
FW	Simon	185
GK	Steven	190
MF	Charlie	180

fiddle

Answer 2

One canonical approach uses the RANK() analytic function:

WITH cte AS (
    SELECT t.*, RANK() OVER (PARTITION BY POSITION ORDER BY HEIGHT DESC) rnk
    FROM TEAM t
)

SELECT POSITION, NAME, HEIGHT
FROM cte
WHERE rnk = 1;