how can i get four last scores of each course in mysql?

Question

my database have four tables: student, course, stu_course, score.

columns for course table: id, name

columns for student table: id, firs_name, last_name, username, password

columns for stu_course table: id, stu_id, course_id (stu_id and cours_id are foreign keys)

columns fir score table: id, stu_cours_id, score, date (stu_cours_id is forein ksy)

My question is how can i get four last scores of each course in score table?
here is what i currently have:

SELECT s.first_name
     , c.name
     , sc.id
     , k.score
     , k.date
     ,  
  FROM student s
  JOIN stu_course sc
    ON sc.stu_id = s.id
  JOIN course c
    ON c.id = sc.course_id
  JOIN score k
    ON k.stu_course_id = sc.id

Thanks for your helps

Version of MySQL: 10.1.37-MariaDB – y.mohammad65 Mar 21 '19 at 23:24 — y.mohammad65, Mar 21 '19 at 23:24

score 1 · Answer 1 · answered Mar 21 '19 at 22:02

1

You can use an ORDER BY statement to sort your list (use order by desc to sort descending) then use LIMIT x to just show the first x results.

Effectively if you use ORDER BY DESC LIMIT x you'll see the bottom x results when sorted by your chosen column

answered Mar 21 '19 at 22:02

tomh1012

274
1
10

1

This doesn't return the last 4 for each course, it only returns the last four from _any_ course. – Bill Karwin Mar 21 '19 at 22:12

GMB · Accepted Answer · 2019-03-22T10:41:47.727

1

In MySQL 8.0 (Maria DB >= 10.2), you could use window function ROW_NUMBER() to rank the scores of each course by descending date. Then, just filter out records with a rank higher than 4:

SELECT *
FROM (
    SELECT student.first_name, course.name, stu_course.id, score.score, score.date,
        ROW_NUMBER() OVER(PARTITION BY course.id ORDER BY score.date DESC) rn
    FROM `student`
    INNER JOIN stu_course ON stu_course.stu_id = student.id
    INNER JOIN course ON course.id = stu_course.course_id
    INNER JOIN score ON score.stu_course_id=stu_course.id
) x WHERE rn <= 4

In earlier versions of MySQL/MariaDB, a solution would be to use a correlated subquery:

SELECT 
    s.first_name, 
    c.name, 
    sc.id, 
    k.score, 
    k.date 
FROM student s 
JOIN stu_course sc ON sc.stu_id = s.id 
JOIN course c ON c.id = sc.course_id 
JOIN score k 
    ON k.stu_course_id = sc.id 
    AND (
        SELECT COUNT(*) FROM score k1 WHERE k1.stu_course_id = k.stu_course_id AND k1.date > k.date
    ) <= 3

edited Mar 22 '19 at 10:41

answered Mar 21 '19 at 22:03

GMB

216,147
25
84
135

thanks for your help – y.mohammad65 Mar 21 '19 at 22:22
@y.mohammad65: welcome! Have you been able to test the query against your database? – GMB Mar 21 '19 at 22:30
this only returns 4 last rows from whole result. i want results which contains 4 last rows of each course – y.mohammad65 Mar 21 '19 at 22:31
@y.mohammad65: yes, that what the `PARTITION BY course.id` clause does... Can you can remove the `WHERE` clause, and make sure that you have more than one distinct `course.id` in your data ? – GMB Mar 21 '19 at 22:34
i think ROW_NUMBER() not work – y.mohammad65 Mar 21 '19 at 22:43
this line return error: ROW_NUMBER() OVER(PARTITION BY course.id ORDER BY score.date DESC) rn – y.mohammad65 Mar 21 '19 at 22:43
@y.mohammad65: which version of MySQL are you using? I mentionned that `ROW_NUMBER()` is available since version 8.0... – GMB Mar 21 '19 at 22:45
what are rn and x? – y.mohammad65 Mar 21 '19 at 22:46
Server version: 10.1.37-MariaDB - mariadb.org binary distribution Protocol version: 10 – y.mohammad65 Mar 21 '19 at 22:51
@y.mohammad65: `x` is an alias for the subquery (it's important in SQL), and `rn` is the row number that was computed in the subquery. Can you please run `SELECT @@VERSION;` and let me know what it returns? – GMB Mar 21 '19 at 22:52
@y.mohammad65: [window functions](https://mariadb.com/kb/en/library/window-functions/) (such as `ROW_NUMBER()`) are available starting MariaDB 10.2, so your version does not support them... – GMB Mar 21 '19 at 22:53
I do not know the meaning of the rn and x in your sql statement – y.mohammad65 Mar 21 '19 at 22:54
is there no other solution? – y.mohammad65 Mar 21 '19 at 22:58
Thanks a lot for your help – y.mohammad65 Mar 21 '19 at 23:02
Can you try this query: `SELECT s.first_name, c.name, sc.id, k.score, k.date FROM student s JOIN stu_course sc ON sc.stu_id = s.id JOIN course c ON c.id = sc.course_id JOIN score k ON k.stu_course_id = sc.id AND (SELECT COUNT(*) FROM score k1 WHERE k1.course_id = k.course_id AND k1.date > k.date) <= 3` – GMB Mar 21 '19 at 23:09
@y.mohammad65: is the new query working for you? – GMB Mar 22 '19 at 01:24
Unfortunately not working – y.mohammad65 Mar 22 '19 at 05:13
your last code return this error: Unknown column 'k1.course_id' in 'where clause' - score table dose not have course_id column – y.mohammad65 Mar 22 '19 at 05:38
@y.mohammad65: sorry - should be ... WHERE k1.stu_course_id = k.stu_course_id – GMB Mar 22 '19 at 07:21
1

it works right. thank you so much for your help. – y.mohammad65 Mar 22 '19 at 09:44
`SELECT s.first_name, c.name, sc.id, k.score, k.date FROM student s JOIN stu_course sc ON sc.stu_id = s.id JOIN course c ON c.id = sc.course_id JOIN score k ON k.stu_course_id = sc.id AND ( SELECT COUNT(*) FROM score k1 WHERE k1.stu_course_id = k.stu_course_id AND k1.date > k.date) <= 3` – y.mohammad65 Mar 22 '19 at 09:45
yesterday I checked for a limited amount of data and worked fine but now that the number of data has increased, it does not work properly – y.mohammad65 Mar 23 '19 at 06:54

how can i get four last scores of each course in mysql?

2 Answers2