dziadek1990 dziadek1990 - 6 months ago 7
SQL Question

Executing a particular type of a nested SELECT

There's an "Employees" table:

| Name | Income | Job_Title |
+--------+----------+-------------+
| Adam | 1000 | ABC |
| Walt | 1200 | ABC |
| Kate | 2000 | XYZ |
| Abel | 2500 | XYZ |
| Judy | 1300 | ABC |
| Jess | 2400 | XYZ |
| ... | ... | ... |


I want to SELECT, for each person: the person's
Name
,
Job_Title
, and the minimal and the maximal
Income
of a person who has the same
Job_Title
, to achieve this:

| Name | Min | Max | Job_Title |
+--------+----------+----------+------------+
| Adam | 1000 | 1300 | ABC |
| Walt | 1000 | 1300 | ABC |
| Kate | 2000 | 2500 | XYZ |
| Abel | 2000 | 2500 | XYZ |
| Judy | 1000 | 1300 | ABC |
| Jess | 2000 | 2500 | XYZ |
| ... | ... | ... | ... |


(I can find/figure out solutions for other types of nested SELECTs, but here... I am stumped.)

jpw jpw
Answer

One way to do get the result you are looking for is to use correlated sub-queries like this:

select 
    name,
    (select min(income) from employees where Job_Title = e.Job_Title) as min,
    (select max(income) from employees where Job_Title = e.Job_Title) as max,
    Job_Title
from employees e

Or you could use a common table expression to get the min/max values for each job title and join with that, like this:

with min_max as (
    select min(income) as min, max(income) as max, Job_Title 
    from Employees 
    group by Job_Title
)

select name, min, max, e.Job_Title
from Employees e 
join min_max mm on e.Job_Title = mm.Job_Title