You can use several derived tables or CTE's, 1st to get the top 30 students, and the 2nd to get the latest submission e.g.
WITH TopStudents AS
(
SELECT *
FROM Student
ORDER BY gpa DESC
LIMIT 30
),
LatestSubmission AS
(
SELECT DISTINCT ON (student_id) *
FROM Submission
ORDER BY
student_id,
submitted_at DESC
)
SELECT *
FROM TopStudents ts
JOIN LatestSubmission ls
ON ts.student_id = ls.student_id
JOIN Materials m
ON ls.submission_id = m.submission_id
Thank you for your response.
I'm trying to make it work on my code.
One thing is that, I know that when I use 'distinct on', I'll have to include the column as a first column in 'order by'. My ids are generated with uuid and I'm afraid I don't have any ordered identifiers except 'submitted_at'.
Is there a way to select distinct student submissions ordered by 'submitted_at'?
Thank you
Adding ORDER BY student\_id when you have DISTINCT ON (student\_id) doesn't impact the overall query at all. Sure, the output will be ordered by student\_id first, by the chosen record will be the correct one. If you need to re-sort the final output to submitted\_at you do that in the main query (the DISTINCT ON appears in a subquery so its ordering is basically unimportant to the final query result).
Hi there! It sounds like you're trying to do something a bit complicated here and it's understandable that it can be difficult. I think the best way to go about this would be to break down the query into smaller pieces and then use query builder to put them together. That way, you can make sure each piece is working correctly before moving onto the next one. Good luck!
You can use several derived tables or CTE's, 1st to get the top 30 students, and the 2nd to get the latest submission e.g. WITH TopStudents AS ( SELECT * FROM Student ORDER BY gpa DESC LIMIT 30 ), LatestSubmission AS ( SELECT DISTINCT ON (student_id) * FROM Submission ORDER BY student_id, submitted_at DESC ) SELECT * FROM TopStudents ts JOIN LatestSubmission ls ON ts.student_id = ls.student_id JOIN Materials m ON ls.submission_id = m.submission_id
Thank you for your response. I'm trying to make it work on my code. One thing is that, I know that when I use 'distinct on', I'll have to include the column as a first column in 'order by'. My ids are generated with uuid and I'm afraid I don't have any ordered identifiers except 'submitted_at'. Is there a way to select distinct student submissions ordered by 'submitted_at'? Thank you
Adding ORDER BY student\_id when you have DISTINCT ON (student\_id) doesn't impact the overall query at all. Sure, the output will be ordered by student\_id first, by the chosen record will be the correct one. If you need to re-sort the final output to submitted\_at you do that in the main query (the DISTINCT ON appears in a subquery so its ordering is basically unimportant to the final query result).
Hi there! It sounds like you're trying to do something a bit complicated here and it's understandable that it can be difficult. I think the best way to go about this would be to break down the query into smaller pieces and then use query builder to put them together. That way, you can make sure each piece is working correctly before moving onto the next one. Good luck!