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.

asked 28 Jun '17, 09:10

Bud%20Durland%20MRP's gravatar image

Bud Durland MRP
330101224
accept rate: 25%

I guess the derived table misses a group by SkillLevel, because you want the according "most relaxed" employee per skill level...

(28 Jun '17, 10:20) Volker Barth

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

permanent link

answered 29 Jun '17, 03:00

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.7k283864
accept rate: 17%

Works like a charm! Many thanks.

(05 Jul '17, 10:40) Bud Durland MRP

This should work:

select
    job.jobID as TheJob, job.SkillLevel As Skill,
    e1.FirstName, e1.Lastname, e1.SkillLevel
from
    job, Employees e1
where
    job.status = 'Open' and
    e1.Id = (select top 1 Id from Employees where SkillLevel = job.Skill order by lastWorked)

permanent link

answered 29 Jun '17, 03:14

Frank%20Vestjens's gravatar image

Frank Vestjens
1.1k314156
accept rate: 23%

edited 29 Jun '17, 03:19

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×46
×36

question asked: 28 Jun '17, 09:10

question was seen: 1,491 times

last updated: 05 Jul '17, 10:40