1

I have this table in a MySQL 5.6 database. It collects the grades (final grade) teachers (usermodified) have awarded students on each of their attempts to each assessment item (itemid), an assessment item can be an assignment, quiz, forum discussion, course overall grade, etc. I only need information on the assignments, so I filter this table on source = 'mod/assign' to fetch that data as follows:

select t1.*
from mytable t1
where t1.source = 'mod/assign'

| id | itemid | source     | userid | finalgrade | usermodified |            feedback           | timemodified |
|:--:|:------:|------------|:------:|:----------:|:------------:|:-----------------------------:|:------------:|
| 1  | 4      | mod/assign | 3      |            | 3            |                               | 1596173158   |
| 15 | 4      | mod/assign | 3      |            | 3            |                               | 1596173203   |
| 16 | 4      | mod/assign | 3      | 2.00000    | 2            | <p>Needs more dragons<br></p> | 1596173324   |
| 18 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>Too many dragons<br></p>   | 1596173326   |
| 19 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>Needs more dragons<br></p> | 1596173339   |
| 20 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>4347jgfvgc<br></p>         | 1596173343   |
| 21 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>Apples and Carrots<br></p> | 1596173350   |
| 22 | 4      | mod/assign | 3      | 3.00000    | 2            | <p>khjgc<br></p>              | 1596173371   |
| 24 | 4      | mod/assign | 4      |            | 4            |                               | 1598234190   |
| 38 | 4      | mod/assign | 4      |            | 4            |                               | 1598234202   |
| 39 | 4      | mod/assign | 4      | 2.00000    | 2            |                               | 1598234334   |
| 41 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234335   |
| 42 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234341   |
| 43 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234356   |
| 44 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234361   |

I want to add a calculated column at the end called Is Latest?, which will say 'Yes' if the attempt is the latest attempt and 'No' if otherwise. However, I don't have a clue on how to do this. I know it has to use id as the column to calculate, as teachers can reneg a grade on an assignment attempt (even if that attempt isn't the latest attempt), which updates timemodified. The output of that query should be as follows:

| id | itemid | source     | userid | finalgrade | usermodified |            feedback           | timemodified | is latest? |
|:--:|:------:|------------|:------:|:----------:|:------------:|:-----------------------------:|:------------:|------------|
| 1  | 4      | mod/assign | 3      |            | 3            |                               | 1596173158   | No         |
| 15 | 4      | mod/assign | 3      |            | 3            |                               | 1596173203   | No         |
| 16 | 4      | mod/assign | 3      | 2.00000    | 2            | <p>Needs more dragons<br></p> | 1596173324   | No         |
| 18 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>Too many dragons<br></p>   | 1596173326   | No         |
| 19 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>Needs more dragons<br></p> | 1596173339   | No         |
| 20 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>4347jgfvgc<br></p>         | 1596173343   | No         |
| 21 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>Apples and Carrots<br></p> | 1596173350   | No         |
| 22 | 4      | mod/assign | 3      | 3.00000    | 2            | <p>khjgc<br></p>              | 1596173371   | Yes        |
| 24 | 4      | mod/assign | 4      |            | 4            |                               | 1598234190   | No         |
| 38 | 4      | mod/assign | 4      |            | 4            |                               | 1598234202   | No         |
| 39 | 4      | mod/assign | 4      | 2.00000    | 2            |                               | 1598234334   | No         |
| 41 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234335   | No         |
| 42 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234341   | No         |
| 43 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234356   | No         |
| 44 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234361   | Yes        |

I've searched google and StackOverflow for simiar situations, but I've come up empty. Someone marked this question as an associate of this one, but I cannot see a way to modify that question's answers to suit my situation.

I appreciate the help!

Zectzozda
  • 77
  • 7

1 Answers1

2

If you are using MySQL 8+, then ROW_NUMBER can be used here:

SELECT *,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY userid ORDER BY timemodified DESC) = 1
         THEN 'Yes' ELSE 'No' END AS IsLatest
FROM yourTable
ORDER BY
    timemodified;

For earlier versions of MySQL, we can join to a subquery which finds the latest modified time for each user:

SELECT t1.*,
    CASE WHEN t1.timemodified = t2.maxtimemodified
         THEN 'Yes' ELSE 'No' END AS IsLatest
FROM yourTable t1
LEFT JOIN
(
     SELECT userid, MAX(timemodified) AS maxtimemodified
     FROM yourTable
     GROUP BY userid
) t2
    ON t1.userid = t2.userid
ORDER BY
    t1.timemodified;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks for the response! I'm using MySQL 5.6. I ran your query against my table, but I didn't get the results I needed. I realise I accidentally omitted some information that may reveal why: the table has a lot of other information from sources other than assignments that students submit attempts against, such as forum grades and course overall grades, so I filter the table so it only displays the assignment submissions. Would that be throwing this out? I've edited the test data to more accurately show the contents. I apologise if my original post was misleading, I'm still bad at DB queries! – Zectzozda Aug 31 '20 at 05:39
  • Also, as your queries are using `timemodified`, is it using that column to identify the latest attempt a student submitted against the assignment? Is it possible to use `id` instead? `timemodifed` can change if a teacher goes back and edits a grade on a previous attempt, which I think your query will mistakenly show as the latest attempt. – Zectzozda Aug 31 '20 at 05:39
  • I have actually answered based on the data you did show. If _neither_ of my queries are working for you, then please add sample data for which the queries would be failing. – Tim Biegeleisen Aug 31 '20 at 05:42
  • My apologies! I've edited my sample data with the data that the queries fail against. [Here](https://imgur.com/a/wnC0CXB) is a screenshot of the output. The only record that is incorrect is `IsLatest` at `id = 22`: it should = `Yes`. – Zectzozda Aug 31 '20 at 05:47