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?

  • 1
    what RDBMS do you use? What does max_score_city mean? Please add data sample and example of desired result. – Nikita Jul 19 at 17:00
  • 1
    Thanks for the suggestions, I have edited now. – Abhishek Singh Jul 19 at 17:13
  • 1
    what about database server version? – Nikita Jul 19 at 17:26
  • What do you want to do about ties? – Vérace Jul 20 at 2:46
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
| improve this answer | |
  • 1
    How will your answer cope with ties? – Vérace Jul 20 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 – Nikita Jul 20 at 7:58
  • @Nikita: This solved my problems, hence accepting the answer. Thanks! – Abhishek Singh Jul 23 at 17:12
  • Thanks @Nikita! – Abhishek Singh Jul 23 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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