Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER


Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE	11.2.0.2.0	Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production


CREATE TABLE record
(
  p_id INTEGER NOT NULL,
  score INTEGER NOT NULL,
  city VARCHAR(15) NOT NULL
);
INSERT INTO record
SELECT * FROM
(
  SELECT 12 AS p_id, 20 AS score, 'Mumbai' AS city FROM dual UNION
  
  SELECT 12, 80, 'Chennai' FROM dual UNION
  SELECT 12, 80, 'Delhi'   FROM dual UNION
  SELECT 12, 50, 'Pune'    FROM dual UNION
  SELECT 13, 20, 'Mumbai'  FROM dual UNION
  SELECT 13, 30, 'Delhi'   FROM dual UNION
  SELECT 13, 50, 'Pune'    FROM dual UNION
  SELECT 13, 90, 'Hyd'     FROM dual
);
8 rows affected

SELECT * FROM record;


P_ID
SCORE
CITY


12
20
Mumbai

12
50
Pune

12
80
Chennai

12
80
Delhi

13
20
Mumbai

13
30
Delhi

13
50
Pune

13
90
Hyd


SELECT MIN(score), MAX(score)
FROM record 
GROUP BY p_id;


MIN(SCORE)
MAX(SCORE)


20
90

20
80



WITH mins AS 
(SELECT P_id
    ,score AS min_score   
    ,city AS min_score_city    
    ,ROW_NUMBER() OVER (PARTITION BY P_id ORDER BY score) AS rn
 FROM record
)
, maxs AS 
(SELECT P_id
    ,score AS max_score   
    ,city AS max_score_city    
    ,ROW_NUMBER() OVER (PARTITION BY P_id ORDER BY score DESC) AS rn
 FROM record
)
SELECT maxs.P_id
    ,maxs.max_score
    ,maxs.max_score_city
    ,mins.min_score
    ,mins.min_score_city
FROM mins
JOIN maxs ON mins.P_id = maxs.P_id
WHERE mins.rn = 1 AND maxs.rn = 1



P_ID
MAX_SCORE
MAX_SCORE_CITY
MIN_SCORE
MIN_SCORE_CITY


12
80
Chennai
20
Mumbai

13
90
Hyd
20
Mumbai

P_ID	SCORE	CITY
12	20	Mumbai
12	50	Pune
12	80	Chennai
12	80	Delhi
13	20	Mumbai
13	30	Delhi
13	50	Pune
13	90	Hyd