dziadek1990 - 1 year ago 52
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.)

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download