...

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



melissa820
10-31-2007, 09: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!

Roelf
10-31-2007, 10: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?

melissa820
10-31-2007, 10: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.

Roelf
11-01-2007, 10: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
WHERE (
s.datechanged = (
SELECT MAX(datechanged)
FROM status s2
WHERE p.username = s2.username))
AND (s.status = 'Current')

melissa820
11-01-2007, 03:02 PM
This is perfect, thank you so much for your help!!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum