Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

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.7k293965
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.3k364866
accept rate: 21%

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,709 times

last updated: 05 Jul '17, 10:40