[SQL] How can I avoid duplicating multi-column correlated subqueries?

I want a correlated sub query return multiple columns and filter the subquery 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;


SQLite and MySQL, neither 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.

Continue reading...