Get the most recent submission from a team in a submissions table (SQL)

Question

I have a table that can be simplified as:

ID  |team_id |  submission file  | date     
========================================
1   |  1756  |  final_project.c  |2018-06-22 19:00:00
2   |  1923  |       asdf.c      |2018-06-22 16:00:00
3   |  1756  |     untitled.c    |2018-06-21 20:00:00
4   |  1923  |   my_project.c    |2018-06-21 14:00:00
5   |  1756  |     untitled.c    |2018-06-21 08:00:00
6   |  1814  |   my_project.c    |2018-06-20 12:00:00

This is a table of people submitting their projects to me, but I only want each individual students' most recent submission, with each student having a unique team_id.

How do I recall the most recent row of each team_id so that my recall looks like this:

ID  |team_id |  submission file  | date   
========================================
1   |  1756  |  final_project.c  |2018-06-22 19:00:00
2   |  1923  |       asdf.c      |2018-06-22 16:00:00
6   |  1814  |   my_project.c    |2018-06-20 12:00:00

Thank you for the help!

My question got lost down there in the comments. Do you want the most recent version of all files the team has submitted or just the most recent single file the team has submitted? — Shawn, Jun 22 '18 at 19:26

Yogesh Sharma · Answer 1 · 2018-06-22T18:06:42.687

1

Subquery will do what you want with correlation approach :

select t.*
from table t -- Need to replace table with your table-name i.e. Projecttble, etc..
where id = (select t1.id
            from table t1 -- Need to replace table with your table-name 
            where t1.team_id = t.team_id 
            order by t1.date desc 
            limit 1
           );

edited Jun 22 '18 at 18:06

answered Jun 22 '18 at 17:38

Yogesh Sharma

49,870
5
26
52

sorry, to clarify what is t & t1? the name of the table? is t1 a dummy variable? – Jun 22 '18 at 18:03
@Amira. . . `t` or `t1` is not a variable it is alise of table. You just need to replace `table` with your original name of `table`. – Yogesh Sharma Jun 22 '18 at 18:04
Add a filter for `submission_file` into your subquery or it will only pull the most recent file for the `team_id`. So in looking back at the OP, it leads me back to the question of @Amira, do you want the most recent version of all files the team has submitted or just the most recent single file the team has submitted? – Shawn Jun 22 '18 at 19:13

score 0 · Answer 2 · answered Jun 22 '18 at 17:39

0

You could use a self join to pick most recent row per team

select a.*
from your_table a
join (
    select team_id, max(date) date
    from your_table 
    group by team_id
) b on a.team_id = b.team_id and a.date = b.date

answered Jun 22 '18 at 17:39

M Khalid Junaid

63,861
10
90
118

Shawn · Answer 3 · 2018-06-22T19:25:54.490

EDIT: Yogesh beat me to the MySQL 5 answer while I was AFK. You'll want to join your subquery on both the team_id and the submission_file in case you get multiple file submissions from a team on the same day.

Depending on what version of MySQL you are using, this can be done different ways.

SETUP

CREATE TABLE t1 (ID int, team_id int, submission_file varchar(30), the_date date) ;

INSERT INTO t1 (ID,team_id,submission_file,the_date)
SELECT 1, 1756, 'final_project.c', '2018-06-20 19:00:00' UNION ALL  
SELECT 2, 1923, 'asdf.c', '2018-06-22 16:00:00' UNION ALL  /**/
SELECT 3, 1756, 'untitled.c', '2018-06-21 20:00:00' UNION ALL  /**/
SELECT 4, 1923, 'my_project.c', '2018-06-21 14:00:00' UNION ALL  /**/
SELECT 5, 1756, 'untitled.c', '2018-06-21 08:00:00' UNION ALL
SELECT 6, 1814, 'my_project.c', '2018-06-20 12:00:00'  UNION ALL/**/

SELECT 7, 1756, 'final_project.c', '2018-06-21 19:00:00' UNION ALL
SELECT 8, 1756, 'final_project.c', '2018-06-22 00:00:00' /**/

;

QUERIES

If you are using MySQL 5.x or lower, then you'll want to use a correlated subquery with a LIMIT on it to pull up just the rows you want.

/* MySQL <8 */
SELECT a.*
FROM t1 a
WHERE a.id = (
    SELECT b.id
    FROM t1 b
    WHERE b.team_id = a.team_id
      AND b.submission_file = a.submission_file
    ORDER BY b.the_date DESC
    LIMIT 1
) ;

ID | team_id | submission_file | the_date  
-: | ------: | :-------------- | :---------
 2 |    1923 | asdf.c          | 2018-06-22
 3 |    1756 | untitled.c      | 2018-06-21
 4 |    1923 | my_project.c    | 2018-06-21
 6 |    1814 | my_project.c    | 2018-06-20
 8 |    1756 | final_project.c | 2018-06-22

MySQL 8 added window functions (FINALLY), and this makes a problem like this MUCH easier to solve, and likely much more efficient, too. You can sort the rows you need with a ROW_NUMBER() window function.

/* MySQL 8+ */
SELECT s1.ID, s1.team_id, s1.submission_file, s1.the_date
FROM (
  SELECT ID, team_id, submission_file, the_date
    , ROW_NUMBER() OVER (PARTITION BY team_id, submission_file ORDER BY the_date DESC) AS rn
  FROM t1
) s1
WHERE rn = 1
;

ID | team_id | submission_file | the_date  
-: | ------: | :-------------- | :---------
 8 |    1756 | final_project.c | 2018-06-22
 3 |    1756 | untitled.c      | 2018-06-21
 6 |    1814 | my_project.c    | 2018-06-20
 2 |    1923 | asdf.c          | 2018-06-22
 4 |    1923 | my_project.c    | 2018-06-21

db<>fiddle here

NOTE: After re-reading the OP, the intent may be different than what I originally read. In my queries, my filtering will return the most recent of all unique submission_file names that a team submitted. So if a team submitted 3 files, you will get all 3 of the most recent versions of those files. If you remove submission_file from the 5 subquery and the 8 PARTITION BY, it will return only the most recent single file a team submitted regardless of name.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e9bcddd0bb4c233c5a606303194b6c98 << Added examples of multiple `submission_file`s for same `team_id` at same time. — Shawn, Jun 22 '18 at 19:06

Get the most recent submission from a team in a submissions table (SQL)

3 Answers3