RachelW
4Penny.net
Points: 7353

11/30/2017 4:09:34 PM

CROSS APPLY example

* This article, and all our great SQL (General) documentation, Is available on the SQL (General) menu

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

4Penny.net
Version: Unknown or N/A
Section: SQL Scripts
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3