Create a view to get latest status for each application

I would like some help creating a view that will display the latest status for each application. The lastest status should be based on CreateDt.

For example:

Table Structure:

============

Application: ApplicationID, Name, Address, City, State, Zip, etc..

ApplicationAction: ApplicationActionID, ApplicationID, Status (Ex:new, reviewed, approved, closed), CreateDt

View should display:

==============

ApplicantID, ApplicantActionID, Status, CreateDt

Example:

==========

ApplicantID=4, Name=Bob Smith, etc...

ApplicantActionID=1, ApplicantID=4, Status=New, CreatDt=1/3/20071:00

ApplicantActionID=2, ApplicantID=4, Status=Reviewed, CreatDt=1/3/2007 2:00

ApplicantActionID=3, ApplicantID=4, Status=Approved, CreatDt=1/4/2007 1:00

... etc...

View should return:

Applicant=4, ApplicantActionID=3, Status=Approved, CreatDt=1/4/2007 1:00

etc...

Hint: Use MAX(CreatDt) to get the information you need.|||

well I got that far, I need help getting beyond that:( Any ideas?

|||Can you post what you have so far?|||

Oh well..

Declare @.ApTable (ApplicationIDint ,Name varchar(50), Addressvarchar(50), Cityvarchar(50), Statevarchar(50), Zipvarchar(50))Declare @.AATable (ApplicationActionIDint, ApplicationIDint, Statusvarchar(50), CreateDtdatetime)Insert into @.ApSELECt 4,'Bob Smith','123 street','SomeCity','SS','12345'UNIONALLSELECT 5,'New Smith','sss','ss','a','4455'Insert into @.AASELECT 1,4,'New','1/3/2007 1:00'UNIONALLSELECT 2,4,'Reviewed','1/3/2007 2:00'UNIONALLSELECT 3,4,'Approved','1/4/2007 1:00'UNIONALLSELECT 1,5,'New','5/24/2006 1:00'Select *from @.ApSelect *from @.AASELECT aa.*FROM (select applicationid,max(Createdt)as MxCreateDtfrom @.AAgroup by applicationid) Xjoin @.AA AAon aa.createdt = X.MXcreatedt