sqlite - How do I repeat a SQL query over a set of records? - Stack Overflow

Say I have a student who hands in exams for different subjects. Students can submit multiple exams for a subject.

You can infer what subjects a student is taking from the exams they have completed.

SELECT subject 
FROM subject
JOIN exam ON subject.idx = exam.subject_idx
JOIN student ON exam._student_idx = student.idx
WHERE student.name = 'Joe Bloggs'

You can also query the latest exam of a given subject & student with the following:

SELECT exam 
FROM exam
JOIN subject ON exam.subject_idx = subject.idx
JOIN student ON exam.student_idx = student.idx
WHERE subject.name = 'math' AND student.name = 'Joe Bloggs'
ORDER BY completed_at DESC
LIMIT 1

Given a certain student 'Joe Bloggs', how would you get the latest tests of all subjects the student takes? The logical way would be to get subjects with the first query, & iterate over the subjects with the second query but I'm not sure how to do that, or if that is the best way.

Say I have a student who hands in exams for different subjects. Students can submit multiple exams for a subject.

You can infer what subjects a student is taking from the exams they have completed.

SELECT subject 
FROM subject
JOIN exam ON subject.idx = exam.subject_idx
JOIN student ON exam._student_idx = student.idx
WHERE student.name = 'Joe Bloggs'

You can also query the latest exam of a given subject & student with the following:

SELECT exam 
FROM exam
JOIN subject ON exam.subject_idx = subject.idx
JOIN student ON exam.student_idx = student.idx
WHERE subject.name = 'math' AND student.name = 'Joe Bloggs'
ORDER BY completed_at DESC
LIMIT 1

Given a certain student 'Joe Bloggs', how would you get the latest tests of all subjects the student takes? The logical way would be to get subjects with the first query, & iterate over the subjects with the second query but I'm not sure how to do that, or if that is the best way.

• sql
• sqlite

Share Improve this question Follow edited Jan 30 at 4:36 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Jan 30 at 1:28 frimannfrimann 11777 bronze badges 1

• Wouldn't that be the same as your last query but with subject.name = removed and limit removed? – shotor Commented Jan 30 at 1:34

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

Untested but it looks to me like you just need SQL Group By:

select 
    max(completed_at) as completed_at,
    subject.name as SubjectName,
    student.name as StudentName
from 
    exam
    inner join subject 
    on exam.subject_idx = subject.idx
    inner join student 
    on exam.student_idx = student.idx
group by 
    subject.name,
    student.name
order by 
    student.name,
    subject.name

These days using a CTE is also a very common solution to such problems:

with cte as (
    select
        exam.completed_at,
        subject.name as SubjectName,
        student.name as StudentName,
        row_number() over (partition by student.name, subject.name order by exam.completed_at desc) as RowNumber
    from 
        exam
        inner join subject 
        on exam.subject_idx = subject.idx
        inner join student 
        on exam.student_idx = student.idx
)
select
    cte.completed_at,
    cte.SubjectName,
    cte.StudentName
from cte 
where cte.RN = 1
order by 
    cte.StudentName,
    cte.SubjectName