Selecting most recent date of joined table 2

[kalvis]

I am using SQL Server 2005. I have two joined tables one for Accounts the other for Work Orders. For each Account there can be many Work Orders. A simple example of this data structure would be the following:

Accounts Work Orders
-------- -------------------
100 100, 1/1/2009, Job1
200 200, 6/1/2009, Job1
100, 12/1/2009, Job2
100, 12/10/2009, Job1
100, 6/15/2009, Job3
200, 1/1/2010, Job2

I am attempting to determine the most recent work orders completed for each account along with the job type. I am not having any issues returning the max date for each account. But, when I attempt to retrieve the additional information about the work order (Job1... etc) I begin to have issues. If someone could give me assitance with designing a query that returns the most recent date from a joined table I would greatly appreciate it.

 

[markros]

I can give you assistance But rather than giving you the query, I'll give you the links explaining the problem in details:

Including an Aggregated Column's Related Values

Including an Aggregated Column's Related Values - Part 2

PluralSight Learning Library

 

[kalvis]

These blogs were extremely helpful Markos. I appreciate you giving the links. I would much rather learn the methods opposed to having it given to me. Not that it matters but I ended up going with the Windows Function - One Stage query. Now that I have a solution and can get my report out I will be going back through each of the solutions to try and completely understand all the options available. Thanks Markos!

 

[markros]

Glad to help! I was a bit hesitant - sometimes people prefer the solution rather than link - glad that in this case I made the right choice.

PluralSight Learning Library