• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3594
  • Last Modified:

SQL QUERY most recent date for a person_id returning all rows

Hi Guys
I've tried for days to figure this problem out
Any help would be great.

I want the query to retrieve the most recent for each person_id along with all information contained within the row.

EXAMPLE TABLE
auto_id  person_id   grade_id   grade_date
1          1           1         12/12/2003
2          2           1         12/06/2003
3          2           2         12/07/2003
4          1           2         12/01/2004
5          1           3         24/03/2004

THE RESULTS SET I WANT IS:-
auto_id  person_id   grade_id   grade_date
3          2           2         12/07/2003
5          1           3         24/03/2004

I HAVE TRIED QUERIES

SELECT person_id, MAX(grade_date)
FROM grade_history
GROUP BY person_id
//But that only gives me two columns i need the whole row

SELECT person_id, grade_id, grade_date
FROM grade_history
WHERE grade_date=(SELECT MAX(grade_date)
              FROM grade_history);

//But that only gives me one result set of the most recent date NOT the most recent date for a person_id

IF you could shed any light on this problem it would be most welcome.


Chad
0
Chad_MacArthur
Asked:
Chad_MacArthur
  • 3
  • 2
1 Solution
 
jdlambert1Commented:
Try this. If you're using Oracle prior to 9i, or another SQL flavor that doesn't support ANSI-standard JOIN syntax, let us know and we'll give you the older structure.

SELECT gh.*
FROM grade_history gh
INNER JOIN (
 SELECT person_id, MAX(grade_date) as grade_date
 FROM grade_history
 GROUP BY person_id
) maxdate m ON gh.grade_date = m.grade_date AND gh.person_id = m.person_id
0
 
jdlambert1Commented:
Fixing a cut/paste error:

SELECT gh.*
FROM grade_history gh
INNER JOIN (
 SELECT person_id, MAX(grade_date) as grade_date
 FROM grade_history
 GROUP BY person_id
) m ON gh.grade_date = m.grade_date AND gh.person_id = m.person_id


And the non-ANSI syntax:

SELECT gh.*
FROM grade_history gh,
( SELECT person_id, MAX(grade_date) as grade_date
 FROM grade_history
 GROUP BY person_id
) m
WHERE gh.grade_date = m.grade_date AND gh.person_id = m.person_id
0
 
montasirmaCommented:
Try the following
SELECT person_id, MAX(grade_id), MAX(grade_data) FROM grade_history
GROUP BY person_id DESC;
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
jdlambert1Commented:
montasirma, it may vary with the version of SQL, but on most systems your query structure will return the highest grade_id and grade_date independent of each other for each person_id. Chad appears to need the most recent grade_id for each person_id, and if so, he needs to correlate the person_id and maximum grade_date with the main table.
0
 
sachinrpCommented:
Hi ,

This simple query will give you desired result set

SELECT * from example
where person_id||grade_date in (select person_id||MAX(grade_date)
FROM example
GROUP BY person_id)
order by grade_date


Sachin
0
 
sachinrpCommented:
replace example table with grade_history
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now