Retrieving the Most Recent Record for Multiple Files

[JipThePeople]

I have a table containing records of files and scores which also contain a date the record was inserted and the file_id as well as other fields.

 

I want to select the most recent score  for each file_id. For instance, there will be multiple records for a file_id corresponding to the latest score assigned to that file. There will be different file_id's in the table each with multiple records. My goal is to select one record per each unique file_id retrieving only the last record inserted (per the submit_date field).

 

I can retrieve a single record (the most recent record inserted) with this query, but I need the most recent record for each unique file_id in the table.

 

SELECT `score` FROM `file_grade_answers` WHERE `submit_date` = (SELECT MAX(`submit_date`) FROM `file_grade_answers`)

 

Here is the table I am using for this question:

 

CREATE TABLE IF NOT EXISTS `file_grade_answers` (

  `filegrade_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `file_id` int(10) unsigned NOT NULL,

  `reviewer_id` varchar(100) NOT NULL,

  `owner_at_review` varchar(100) NOT NULL,

  `process_at_review` int(10) unsigned NOT NULL,

  `question_ids` varchar(100) NOT NULL,

  `answers` varchar(100) NOT NULL,

  `recommendations` blob NOT NULL,

  `submit_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `possible_points` int(10) unsigned NOT NULL,

  `actual_points` int(10) unsigned NOT NULL,

  `score` int(10) unsigned NOT NULL,

  PRIMARY KEY (`filegrade_id`)

)

 

Any advice will be greatly appreciated.

[fenway]

I'd recommend a self join on submit_date.

[JipThePeople]

I'd recommend a self join on submit_date.

 

Can you please provide an example?

[fenway]

SELECT a.`score` FROM `file_grade_answers` AS a INNER JOIN `file_grade_answers` AS b ON ( a.file_id = b.file_id and a.submit_date > b.submit_date )

[JipThePeople]

SELECT a.`score` FROM `file_grade_answers` AS a INNER JOIN `file_grade_answers` AS b ON ( a.file_id = b.file_id and a.submit_date > b.submit_date )

 

Thx so much for the response. My table has 9 records (currently) for 3 different file id's (i.e., I need to retrieve the most recent record for each file id = 3 records returned).

 

Unfortunately, the query you provided returns multiple records for each file_id. Any other ideas will be great appreciated.

[fenway]

Please post the relevant insert statements so that I can test it on my end -- that was supposed to work.

[fenway]

Oops -- missing the "a.filegrade_id = b.filegrade_id" equality.

[JipThePeople]

Please post the relevant insert statements so that I can test it on my end -- that was supposed to work.

 

Here is an insert as requested. Thx for your efforts.

 

INSERT INTO `file_grade_answers`

(`file_id`, `reviewer_id`, `owner_at_review`, `process_at_review`, `question_ids`, `answers`, `recommendations`, `submit_date`, `possible_points`, `actual_points`, `score`)

VALUES ('451', 'blackblue21', 'jrwoodsman24', '2', '1,2,3,4,5,6,7', '1,2,1,3,5,6,2', 'My recommendation text', NOW(), '35', '20', '57')

[fenway]

That's a single record -- I'll need many.

[JipThePeople]

That's a single record -- I'll need many.

 

INSERT INTO `file_grade_answers` (`filegrade_id`, `file_id`, `reviewer_id`, `owner_at_review`, `process_at_review`, `question_ids`, `answers`, `recommendations`, `submit_date`, `possible_points`, `actual_points`, `score`) VALUES

(1, 451, 'gillian', 'coverdale', 2, '1,2,3,4', '2,1,1,2', 0x54657374696e6720313233, '2011-08-24 09:27:33', 20, 6, 30),

(2, 453, 'hughes', 'dimarzio', 11, '1,2,3', '4,4,4', '', '2011-08-24 10:58:05', 15, 12, 80),

(3, 452, 'blackmore', 'dimarzio', 5, '1,2,3,4,5,6,7', '3,2,1,1,2,3,1', '', '2011-08-24 10:59:39', 35, 13, 37),

(4, 452, 'dimarzio', 'blackmore', 5, '1,2,3,4,5,6,7', '4,5,4,5,5,5,3', '', '2011-08-25 09:39:39', 35, 31, 89),

(5, 451, 'dimarzio', 'dimarzio', 2, '1,2', '4,5', '', '2011-08-25 10:29:13', 10, 9, 90),

(6, 452, 'dimarzio', 'blackmore', 5, '1,2', '3,3', '', '2011-08-26 08:09:20', 10, 6, 60),

(7, 452, 'bolin', 'paice', 5, '1', '3', '', '2011-08-26 08:09:45', 5, 3, 60),

(8, 452, 'jlord', 'glover', 10, '1,2,3,4', '5,5,5,5', 0x546869732066696c652069732067726561746e6573732e, '2011-08-29 12:46:20', 20, 20, 100),

(9, 453, 'jlord', 'paice', 11, '1,2,3,4,6', '4,5,4,5,5', 0x536f6d657468696e672068617320676f6e652061206269742061736b6577206f6e207468652074726564646c652e, '2011-08-29 20:42:56', 25, 23, 92);

[fenway]

SELECT a.file_id, a.score, a.submit_date
FROM `file_grade_answers` AS a
left JOIN `file_grade_answers` AS b ON ( ( a.file_id ) = ( b.file_id ) and a.submit_date < b.submit_date )
where b.submit_date is null;

 

Sorry, it's been that kind of day -- basically, you're asking for all answers for which there is no more recent answer, for a given file_id.

[JipThePeople]

SELECT a.file_id, a.score, a.submit_date
FROM `file_grade_answers` AS a
left JOIN `file_grade_answers` AS b ON ( ( a.file_id ) = ( b.file_id ) and a.submit_date < b.submit_date )
where b.submit_date is null;

 

Sorry, it's been that kind of day -- basically, you're asking for all answers for which there is no more recent answer, for a given file_id.

 

Thank you so much for your time. That is the solution!