CODEWITHSUNDEEP
sqlpostgresql
AndreFeijo
AndreFeijo

Reputation: 10629

Select multiple columns from row based on MAX value of a column

I am trying to create a summary with the best score of each student per day.

Example data:

╔════╦════════════╦═══════╦═══════════════════╗
║ id ║ student_id ║ score ║ date_time         ║
╠════╬════════════╬═══════╬═══════════════════╣
║ 1  ║ 1          ║ 5     ║ 2018-07-01 9:30   ║
║ 2  ║ 1          ║ 3     ║ 2018-07-01 15:30  ║
║ 3  ║ 1          ║ 7     ║ 2018-07-02 8:30   ║
║ 4  ║ 2          ║ 7     ║ 2018-07-01 9:30   ║
║ 5  ║ 2          ║ 8     ║ 2018-07-01 15:30  ║
║ 6  ║ 2          ║ 8     ║ 2018-07-02 8:30   ║
║ 7  ║ 3          ║ 4     ║ 2018-07-02 10:30  ║
║ 8  ║ 3          ║ 10    ║ 2018-07-02 13:45  ║
╚════╩════════════╩═══════╩═══════════════════╝

Desired result:

╔════════════╦════════════╦═════════════╦═════════════╦══════════════════════╗
║ student_id ║ date       ║ score_total ║ best_score  ║ best_score_date_time ║
╠════════════╬════════════╬═════════════╬═════════════╬══════════════════════╣
║ 1          ║ 2018-07-01 ║ 8           ║  5          ║ 2018-07-01 9:30      ║
║ 1          ║ 2018-07-02 ║ 7           ║  7          ║ 2018-07-02 8:30      ║
║ 2          ║ 2018-07-01 ║ 15          ║  8          ║ 2018-07-01 15:30     ║
║ 2          ║ 2018-07-02 ║ 8           ║  8          ║ 2018-07-02 8:30      ║
║ 3          ║ 2018-07-02 ║ 14          ║  10         ║ 2018-07-02 13:45     ║
╚════════════╩════════════╩═════════════╩═════════════╩══════════════════════╝

This is what I got so far (and my question):

SELECT 
  student_id,
  date_time::date as date,
  SUM(score) as score_total,
  MAX(score) as best_score,
  date_time as best_score_date_time -- << HOW TO GET THIS ONE?
FROM
  score
GROUP BY
  student_id,
  date_time::date

Upvotes: 0

Views: 65

Answers (2)

Sabari
Sabari

Reputation: 244

Below is the simple solution.

Replace 

date_time as best_score_date_time

with

(
  array_agg(date_time order by score desc)
)[1]

Upvotes: 5

Teja
Teja

Reputation: 13524

     SELECT A.student_id,
            A.date,
            A.score_total,
            A.score AS best_score,
            A.best_score_date_time
       FROM   
       (
        SELECT student_id,
               date_time::date AS date,
               SUM( score ) OVER ( PARTITION BY date_time::date ) AS score_total,
               score,
               RANK( ) OVER ( PARTITION BY date_time::date ORDER BY score DESC ) AS rnk,
               date_time
          FROM score
        ) A
   WHERE A.rnk = 1;

Upvotes: 2

Related Questions