|
This could easily be my lack of knowledge about SQL syntax, but the SQL/Anywhere 16 docs, and Google haven't yielded much happiness Consider (and please forgive typos, as I'm shooting from the hip): Create Table JobList ( JobNumber int not null autoincrement, SkillLevel int not null, Status varchar(10) ) Create Table Employees ( id Int not null autoincrement, FirstName varchar(30), LastName varchar(50), LastWorked datetime, SkillLevel int ) Now, for every job in status 'open', I want the first and last name of the employee who hasn't worked the longest, and has the matching SkillLevel. So this would work: select job.jobID as TheJob, job.SkillLevel As Skill, (select top 1 firstname from Employees where skilllevel = skill order by LastWorked) as FName, (select top 1 lastname from Employees where skilllevel = skill order by LastWorked) as LName from job where job.status = 'Open' It seems some variation of this should work, but I haven't got it yet: select job.jobID as TheJob, job.SkillLevel As Skill, e1.FirstName, e1.Lastname, e1.SkillLevel from job, (select top 1 employees.FirstName, Employees.lastname, Employees.SkillLevel order by LastWorked) as e1 where job.status = 'Open' and e1.SkillLevel = jobs.SkillLevel Any guidance appreciated. |
|
Take a look in outer apply select job.jobID as TheJob, job.SkillLevel As Skill, e1.FirstName, e1.Lastname from job outer apply (select top 1 employees.FirstName, Employees.lastname where Employees.SkillLevel = jobs.SkillLevel order by LastWorked) as e1 where job.status = 'Open' Other possibility would be a WITH clause. HTH Works like a charm! Many thanks.
(05 Jul '17, 10:40)
Bud Durland MRP
|
|
This should work: select |
I guess the derived table misses a group by SkillLevel, because you want the according "most relaxed" employee per skill level...