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.
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;