I have a table Record with three columns: p_id, score, city.
P_id score city 12 20 Mumbai 12 80 Delhi 12 50 Pune 13 20 Mumbai 13 30 Delhi 13 50 Pune 13 90 Hyd
Here I want to have a view with p_id, max_score, max_score_city, min_score, min_score_city. Which means for each player, I want max score and the city in which it was scored, min score and the city in which it was scored,
p_id max_score max_score_city min_score min_score_city 12 80 Delhi 20 Mumbai 13 90 Hyd 30 Mumbai
I know I need to apply group by on p_id for min and max score, but how do I join both group by and how to relate player and city in them.
I am new to SQL, can someone please help?