nested/sub query in sql
Posted on 2013-11-07
Hi I have two tables, one called vwApplicationCurrent and one called vwApplicationCategoryCurrent. The tables are a one-many relationship based on Ref no. There can be many Categories assigned to one Application.
I am trying to find the most recent Application Category (would be the row with the max CatAllocationDate) and return the Category Code along with other fields as shown below.
I am a newbie to SQL and am thinking this should be simple but just can't get it. Here is the sample bit of data
RefNo ApplicationDate CurrentApplication
2063 31/10/2013 Yes
RefNo CatCode CatAllocationDate
2063 BN 25/08/2013
2063 NO 26/08/2013
2063 ME 27/08/2013
I am therefore looking to return the following from my view/sql statement:
RefNo ApplicationDate CatCode CatAllocationDate CurentApplication
2063 31/10/2013 ME 27/08/2013 Yes
Many thanks in advance.