View Full Version : SQL Server 2005: Using MAX in Query

Oct 31st, 2007, 08:50 PM
I have two tables: one with person information and the other with a person's status changes/updates. (A person's status can go from Prospective to Current to Completed.)

Person Table:
UserName FullName
jsmith Joe Smith
bjones Bob Jones
cblack Carol Black

Status Table:
UserName Status DateChanged
jsmith Prospective 1/1/2006
bjones Prospective 1/2/2006
jsmith Current 3/3/2006
cblack Prospective 3/3/2006
bjones Current 4/4/2006
bjones Completed 5/5/2006

From the Status Table it's obvious that "bjones" is Completed, "cblack" is Prospective, and "jsmith" is Current. But I'm not sure how to grab this info from these tables in a select. I'm thinking I might need the MAX(DateChanged) in there somewhere, but I have no clue how to use it.

Eventually my goal is to say "show me all the names of the people who are Current".

Any ideas? Thanks in advance, I hate SQL!

Oct 31st, 2007, 09:29 PM
select fullname
from person
inner join status
on person.username = status.username
where status = 'Current'

unless, the person can downgrade, because then a record with status current will be returned while the person is not current anymore
Is that the case?

Oct 31st, 2007, 09:33 PM
Thank you for your response!

They can't downgrade their status, but they can upgrade. I think the query that you posted would return both Joe Smith and Bob Jones. However, Bob's most recent status is Completed so he should not show up in the Current list anymore. A query that would show me all the names of people who are current should only show Joe Smith.

I need for the query to only count a person's most recent status (a.k.a. the one with the most recent date in the DateChanged column of the Status table.

Nov 1st, 2007, 09:09 AM
Sorry, i did not notice that current was not the last status. But here is a query that works:

SELECT p.fullname, s.status, s.datechanged
FROM person p
INNER JOIN status s
ON s.username = p.username
s.datechanged = (
SELECT MAX(datechanged)
FROM status s2
WHERE p.username = s2.username))
AND (s.status = 'Current')

Nov 1st, 2007, 02:02 PM
This is perfect, thank you so much for your help!!