Ok, get your thinking caps on. The customer requirement is this:
We have a JOB table and a JOBSTATUS table, display the Job and the latest Status
Here is our data:
declare @job as table(Job varchar(15))
insert into @job (Job) values ('Job1')
insert into @job (Job) values ('Job2')
declare @log as table (RowID int identity, Job varchar(15), Status varchar(10))
insert into @log(job, status) values ('Job1','Starting')
insert into @log(job, status) values ('Job1','Step 1')
insert into @log(job, status) values ('Job1','Step 2')
insert into @log(job, status) values ('Job2','Starting')
insert into @log(job, status) values ('Job2','Step 1')
insert into @log(job, status) values ('Job2','Step 2')
insert into @log(job, status) values ('Job2','Step 3')
insert into @log(job, status) values ('Job2','Done')
Given this, how would you write a query to return this?
See my answer below... but I'd like to hear yours