sqlsqlitecorrelated-subquery

How can I avoid duplicating multi-column correlated subqueries?


I want a correlated sub query that returns multiple columns and filters itself based on values in the main table.

A list of student test scores for a set of days:

create table student
(id, name     ); insert into student values
(1 , 'joe'    ),
(2 , 'steve'  );

create table testScore
(id, studentId, day  , score); insert into testScore values
(1 , 1        , 5    , 70   ),
(2 , 1        , 10   , 68   ),
(3 , 1        , 15   , 95   ),
(4 , 1        , 20   , 81   ),
(5 , 2        , 5    , 100  ),
(6 , 2        , 10   , 75   ),
(7 , 2        , 15   , 98   ),
(8 , 2        , 20   , 92   );
create index testScore_studentTopScores on testScore (studentId, score, day);

If I need to get the top test score for each student (and include the corresponding day numbers, and have the testScore_studentTopScores index be used) I would love to do:

select
  student.name,
  topScore.score as topScore,
  topScore.day as topScoreDay
from
  student
  left join (
    select * from
      testScore
    where
      testScore.studentId = student.id
    order by
      score desc
    limit 1
  ) as topScore
order by
  topScore desc;

And have it return:

name topScore topScoreDay
steve 100 5
joe 95 15

But from clause subqueries can't reference outer/parent tables. Column subqueries and where clause subqueries are able to access tables in the from clause, but cannot return multiple columns. So I have to duplicate the subquery for every column:

select
  student.name,

  (
    select
      score
    from
      testScore
    where
      testScore.studentId = student.id
    order by
      score desc
    limit 1
  )  as topScore,

  (select day from (
    select
      score,
      day
    from
      testScore
    where
      testScore.studentId = student.id
    order by
      score desc
    limit 1
  ))  as topScoreDay

from
  student
order by
  topScore desc;

Neither SQLite nor MySQL can do this without duplication for each column. Microsoft SQL Server has an outer apply join operator; like a left join but where the clause can reference prior tables in the from clause. That's what I want in SQLite.

Also, the solution should not be an SQL trick to just handle this simple example. It needs to be general enough to be scalable for any large and complex query.


Solution

  • Efficient because it only needs one lookup for each student; jam columns I want into one JSON column and 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
            testScore.score desc
          limit 1
        ) as topScoreJson
      from
        student
    ) as topScore
    order by
      topScore desc;