SQL Show most recent record in GROUP BY?

group-by, mysql, sql, sql-order-by

Solution

Start with this:

select StudentId, max(DateApproved) 
from tbl
group by StudentId

Then integrate that to main query:

select * 
from tbl
where (StudentId, DateApproved) in

(
  select StudentId, max(DateApproved) 
  from tbl
  group by StudentId
)

You can also use this:

select * 
from tbl
join (select StudentId, max(DateApproved) as DateApproved 
      from tbl 
      group by StudentId)
using (StudentId, DateApproved)

But I prefer tuple testing, it's way neater

Live test: http://www.sqlfiddle.com/#!2/771b8/5

Problem

I have a table that looks like this: ``` id | SubjectCode | Grade | DateApproved | StudentId 1 SUB123 1.25 1/4/2012 2012-12345 2 SUB123 2.00 1/5/2012 2012-12345 3 SUB123 3.00 1/5/2012 2012-98765 ``` I'm trying to GROUP BY SubjectCode but i'd like it to display the most recent DateApproved so it will look like: ``` id | SubjectCode | Grade | DateApproved | StudentId 2 SUB123 2.00 1/5/2012 2012-12345 3 SUB123 3.00 1/5/2012 2012-98765 ``` I'm a little bit lost on how to do it? EDIT: Ok guys now im on my real PC, sorry for the poorly constructed question. Here's what I'm actually trying to do: ``` SELECT GD.GradebookDetailId, G.SubjectCode, G.Description, G.UnitsAcademic, G.UnitsNonAcademic, GD.Grade, GD.Remarks, G.FacultyName, STR_TO_DATE(G.DateApproved, '%m/%d/%Y %h:%i:%s') AS 'DateAproved' FROM gradebookdetail GD INNER JOIN gradebook G ON GD.GradebookId=G.GradebookId WHERE G.DateApproved IS NOT NULL AND G.GradebookType='final' AND StudentIdNumber='2012-12345' GROUP BY <?????> ORDER BY G.SubjectCode ASC ``` Basically, I only want to display the most recent "DateApprove" of a "SubjectCode", so I don't get multiple entries.

Original source