0

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?

4
  • 1
    what RDBMS do you use? What does max_score_city mean? Please add data sample and example of desired result. Jul 19, 2020 at 17:00
  • 1
    Thanks for the suggestions, I have edited now. Jul 19, 2020 at 17:13
  • 1
    what about database server version? Jul 19, 2020 at 17:26
  • What do you want to do about ties?
    – Vérace
    Jul 20, 2020 at 2:46

2 Answers 2

2
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
)
, 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
)
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
3
  • 1
    How will your answer cope with ties?
    – Vérace
    Jul 20, 2020 at 2:48
  • 2
    @Vérace it depends on how author would like to see the list of corresponding cities. For example, we can use RANK instead of ROW_NUMBER and GROUP BY the result using GROUP_CONCAT or STRING_AGG to concatenate the list of cities Jul 20, 2020 at 7:58
  • @Nikita: This solved my problems, hence accepting the answer. Thanks! Jul 23, 2020 at 17:12
2

You can do this with a single statement:

SELECT
  p_id
 ,MAX
    (
      CASE
        WHEN ScoreDesc = 1 THEN score
        ELSE NULL
      END
    ) AS max_score
 ,MAX
    (
      CASE
        WHEN ScoreDesc = 1 THEN city
        ELSE NULL
      END
    ) AS max_score_city
 ,MAX
    (
      CASE
        WHEN ScoreAsc = 1 THEN score
        ELSE NULL
      END
    ) AS min_score
 ,MAX
    (
      CASE
        WHEN ScoreAsc = 1 THEN city
        ELSE NULL
      END
    ) AS min_score_city
FROM
(
  SELECT
    p_id
   ,score
   ,city
   ,ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY Score, city) AS ScoreAsc
   ,ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY Score DESC, city) AS ScoreDesc
  FROM
    record
) RecordSummary
WHERE
  ScoreAsc = 1
    OR ScoreDesc = 1
GROUP BY 
  p_id

Should be mentioned in case of ties the min/max city will be chosen alphabetically.

3
  • 2
    actually there should be OR in WHERE part Jul 20, 2020 at 14:37
  • @Nikita thanks for the catch. Fixed.
    – user212533
    Jul 20, 2020 at 14:40
  • @bbaird: Thanks for the answer, upvoted! Jul 23, 2020 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.