79109491

Date: 2024-10-21 09:53:56
Score: 1
Natty:
Report link

ok, I had some inspiration after posting to SO (and examining the excellent answers I received, thanks guys!). I think this solution is easy to understand and is usable in any situation like this. It should also be quite efficient because it only needs to do one lookup for each student.

Basically, the whole problem is that you can't return multiple columns in a column based subquery... Well, I can just jam all the columns I want into one JSON column and then extract them later:

select 
  topScore.name,
  topScore.topScoreJson ->> 'score' as topScore,
  topScore.topScoreJson ->> 'day' as topScoreDay
from (  
  select
    student.name,
    (
      select
        json_object(
          'score', testScore.score,
          'day', testScore.day
        )  
      from 
        testScore 
      where 
        testScore.studentId = student.id
      order by 
        score desc
      limit 1  
    ) as topScoreJson
  from
    student
) as topScore   
order by
  topScore desc;
Reasons:
  • Blacklisted phrase (0.5): thanks
  • Whitelisted phrase (-1): solution is
  • RegEx Blacklisted phrase (1): I want
  • Long answer (-0.5):
  • Has code block (-0.5):
  • Self-answer (0.5):
  • Low reputation (1):
Posted by: Bill F.