MySql - Getting rowid / order in a complex query

Question

Working on a project with students/grades/etc, I need to update the top 3 students every once in a while. I came up with the query below. However, I am having trouble getting their rank/order. I know how to do that in a simple query, but in a more complex one, it is not working. I am getting all of the other columns correctly, and, with all the methods I tried to get the order by, I sometimes got 0 (like the current state of the code), sometimes values that are just wrong (1, 11, 10), etc.

NOTE: I have checked various questions (including the question below), but I just couldn't figure out how to place them in my query.

What is the best way to generate ranks in MYSQL?

Summary:

GOAL:
- Get sum of each students' marks from marks, divide that on the number of entries in the table (again marks). Students are from a given grade.
- Use sum(mark) to rank these students.
- Get the top three.
- Place the top three students from that grade in the TopStudents table, with their average marks (as sum) and their id's.

TABLES:

Students table contains info about student including id:

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | int   (20) unsigned | NO   | PRI | NULL    | auto_increment |
| name        |varchar(20) unsigned | NO   |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

Marks Table has marks of each student on each exam

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          |int    (20) unsigned | NO   | PRI | NULL    | auto_increment |
| idStudent   |int    (20) unsigned | NO   | FOR | NULL    |                |
| mark        |tinyInt (3) unsigned | NO   |     | NULL    |                |
| idExam      |int    (20) unsigned | NO   | FOR | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

Grade Table has grade id and name:

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | int   (20) unsigned | NO   | PRI | NULL    | auto_increment |
| name        |varchar(20) unsigned | NO   |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

Class Table classes for each grade. References table

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | int   (20) unsigned | NO   | PRI | NULL    | auto_increment |
| name        |varchar(20) unsigned | NO   |     | NULL    |                |
| idGrade     |  int  (20) unsigned | NO   | FOR | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

and finally, the infamous TopStudents Table .

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | int   (20) unsigned | NO   | PRI | NULL    | auto_increment |
| idStudent   | int   (20) unsigned | NO   | FOR | NULL    |                |
|  sumMarks   | int   (20) unsigned | NO   |     | NULL    |                |
|  rank       |tinyInt (1) unsigned | NO   |     | NULL    |                |
|  date       |date     unsigned    | NO   |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

ATTEMPTS: Attempt 1: ERROR: all ranks are 0

INSERT INTO topStudents(`date`, idStudent, `sum`, `order`)
SELECT
    '2018-10-10' AS DATE,
    student.id AS idStudent,
    AVG(marks.mark)
    @n = @n + 1 AS `order`
FROM
    marks
INNER JOIN student ON student.id = marks.idStudent
INNER JOIN class ON class.id = marks.idClass
INNER JOIN grade ON class.idGrade = grade.id
WHERE
    grade.id = 2
GROUP BY
    marks.idStudent
ORDER BY
    SUM(mark)
DESC
LIMIT 3

Attempt 2: ranks returned: 1, 11, 10

SET @n := 0;


INSERT INTO topStudents(`date`, idStudent, `sum`, `rank`)
SELECT
    '2018-10-10' AS DATE,
    tbl.idStudent AS idStudent,
    AVG(tbl.mark) AS `sum`,

    rnk AS `rank`
FROM (SELECT student.id AS idStudent, SUM(mark) AS mark FROM
    marks
INNER JOIN student ON student.id = marks.idStudent
INNER JOIN class ON class.id = marks.idClass
INNER JOIN grade ON class.idGrade = grade.id
WHERE
    grade.id = 2
GROUP BY
    marks.idStudent
ORDER BY
    SUM(mark)
DESC
LIMIT 3) AS tbl, (SELECT @n = @n + 1) AS rnk

See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Nov 17 '18 at 11:04

Gordon Linoff · Accepted Answer · 2018-11-17T20:29:02.947

0

In more recent versions of MySQL, you need to use a derived table for the ordering, before assigning the ranks:

INSERT INTO topStudents (`date`, idStudent, `sum`, `order`)
    SELECT date, idStudent, `sum`, (@n := @n + 1) AS `order`
    FROM (SELECT '2018-10-10' AS DATE, s.id AS idStudent,
                 SUM(m.mark) / (SELECT COUNT(*) FROM marks m2 WHERE m2.idStudent = m.idStudent) AS `sum`    
          FROM marks m JOIN
               student s
               ON s.id = m.idStudent JOIN
               class c
               ON c.id = m.idClass JOIN
               grade g
               ON c.idGrade = g.id
          WHERE g.id = 2
          GROUP BY m.idStudent
          ORDER BY SUM(mark) DESC
          LIMIT 3
         ) sm CROSS JOIN
         (SELECT @n := 0) params;

I am almost certain that the calculation for sum is incorrect, and that you really intend avg(mark). However, this is the logic you have in your question.

edited Nov 17 '18 at 20:29

answered Nov 17 '18 at 12:00

Gordon Linoff

1,242,037
58
646
786

For simplicity's sake, I trimmed some of the query, which led to that `sum` mistake. In reality I need to divide it by other parameters which are not shown in my question, so I thought this will do. Anywho, your query returned results similar to one of my test (I tried so many I can't remember) : order (ranks) where all 0. – hman_codes Nov 17 '18 at 12:19
@skullz . . . There was a typo -- the assignment needs to use `:=`, not `=`. – Gordon Linoff Nov 17 '18 at 20:29
Worked like a charm. Many thanks – hman_codes Nov 17 '18 at 21:00

MySql - Getting rowid / order in a complex query

1 Answers1