SubQueries 1

[neilmcdonald]

Hi,

I have two tables, Job and Event. A Job has several events and the tables are linked by jobid. Each Event also has a status.

I need to find the status of the last event for each job.

I've gotten as far as finding the last event for each job by doing the following:

SELECT     A.MaxDateTime, Job.JobID
FROM         (SELECT     JobID, MAX(EvDateTime) AS MaxDateTime
                       FROM          Event
                       GROUP BY JobID) AS A INNER JOIN
                      Job ON A.JobID = Job.JobID

But I do not know how to also get the status.

I'd be grateful if somebody could point me in the right direction!

Thanks,

Neil

 

[gmmastros]

This should do it:

[COLOR=blue]SELECT[/color] A.MaxDateTime, Job.JobID, Event.*
[COLOR=blue]FROM[/color]   ([COLOR=blue]SELECT[/color] JobID, [COLOR=#FF00FF]MAX[/color](EvDateTime) [COLOR=blue]AS[/color] MaxDateTime
        [COLOR=blue]FROM[/color]   Event
        [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] JobID) [COLOR=blue]AS[/color] A 
       [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] Event
         [COLOR=blue]On[/color] A.JobId = Event.JobId
         And A.MaxDateTime = Event.evDateTime
       [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] Job 
			[COLOR=blue]ON[/color] A.JobID = Job.JobID

If this doesn't do what you expect, or you have any questions, let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[neilmcdonald]

That's exactly what I need - Thanks!