Group by Max Query

Could any one help me how the Query should look like
I have three tables: Asset_Table, Assignment_Table and User_Table
Output should be: Asset_Number from Asset_Table where Asset_Number = 123, Date from Assignment_Table = MAX Date and First Name from User_Table
Thnx
PatrickMaybe
select asset_number, firstname , (select max(date) from Assignment_table
where assignment_table.id = asset_table.id)
from assignment_Table inner join User_table on assignment_Table.key =user_table.key
where asset_number = 123
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Patrick" <patrick.thie@.nl.mci.com> wrote in message
news:57B2D2A0-9656-4700-9616-E7A60EF4BEC9@.microsoft.com...
> Could any one help me how the Query should look like!
> I have three tables: Asset_Table, Assignment_Table and User_Table.
> Output should be: Asset_Number from Asset_Table where Asset_Number = 123,
Date from Assignment_Table = MAX Date and First Name from User_Table.
> Thnx,
> Patrick