A subquery example
We happened recently to solve a problem of this type:
we had a structure in which, for each category (shown in a table A) had a number of items of detail (in a table B) numeric values (the latter given in a table C that implemented them a one to many relationship between A and B).
We were required to get the maximum value for each category and detail which he was associated.
The following example is inspired from the problem that we faced.
For obvious reasons, the logic was applied to the sample tables are different from the real ones, as long as it was kept the same situation.
Also for example, we have simplified the situation by reducing it to 2 tables, since in any case the one to many relationship is not complicated logic of the query.
The tables chosen for the example are tables DEPARTMENT EMPLOYEES and HR sample schema of Oracle (for completeness, are reported below the DDL to create them).
In this case, we will find, for each department, the employee with the highest salary.
We start with finding the maximum salary for each department.
select max (E2.SALARY) as CALC, E2.DEPARTMENT_ID
E2 from the EMPLOYEES
group by E2.DEPARTMENT_ID
Now we just have to find which dependent (which department) is associated with the wages found.
To do this we create a join with the previous query which satisfies the conditions
QUERY_PRECEDENTE.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID and
QUERY_PRECEDENTE.CALC = EMPLOYEES.SALARY
In this way only going to display employees who receive a salary equal to the maximum for their department.
The final result is the following:
select D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME, MAXSALARY.CALC
from
(select max(E2.SALARY) as CALC, E2.DEPARTMENT_ID from EMPLOYEES E2 group by E2.DEPARTMENT_ID) MAXSALARY,
DEPARTMENTS D,
EMPLOYEES E
where
MAXSALARY.DEPARTMENT_ID = D.DEPARTMENT_ID and
MAXSALARY.CALC = E.SALARY and
E.DEPARTMENT_ID = D.DEPARTMENT_ID
order by 1, 4, 3;
DEPARTMENT_NAME FIRST_NAME LAST_NAME CALC
—————————— ——————– ————————- ———-
Accounting Shelley Higgins 12000
Administration Jennifer Whalen 4400
Executive Steven King 24000
Finance Nancy Greenberg 12000
Human Resources Susan Mavris 6500
IT Alexander Hunold 9000
Marketing Michael Hartstein 13000
Public Relations Hermann Baer 10000
Purchasing Den Raphaely 11000
Sales John Russell 14000
Shipping Adam Fripp 8200
Here the DDL used for the examples.
desc employees
Name Null? Type
—————————————– ——– —————————-
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
desc departments
Name Null? Type
—————————————– ——– —————————-
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
——————————————————–
— DDL for Table DEPARTMENTS
——————————————————–
CREATE TABLE “DEPARTMENTS”
( “DEPARTMENT_ID” NUMBER(4,0),
“DEPARTMENT_NAME” VARCHAR2(30 BYTE),
“MANAGER_ID” NUMBER(6,0),
“LOCATION_ID” NUMBER(4,0)
);
REM INSERTING into DEPARTMENTS
SET DEFINE OFF;
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (’10’,’Administration’,’200′,’1700′);
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (’20’,’Marketing’,’201′,’1800′);
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (’30’,’Purchasing’,’114′,’1700′);
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (’40’,’Human Resources’,’203′,’2400′);
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (’50’,’Shipping’,’121′,’1500′);
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (’60’,’IT’,’103′,’1400′);
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (’70’,’Public Relations’,’204′,’2700′);
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (’80’,’Sales’,’145′,’2500′);
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (’90’,’Executive’,’100′,’1700′);
…
…
Sales’,null,’1700′);
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (‘250′,’Retail Sales’,null,’1700′);
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (‘260′,’Recruiting’,null,’1700′);
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (‘270′,’Payroll’,null,’1700′);
——————————————————–
— DDL for Table EMPLOYEES
——————————————————–
CREATE TABLE “EMPLOYEES”
( “EMPLOYEE_ID” NUMBER(6,0),
“FIRST_NAME” VARCHAR2(20 BYTE),
“LAST_NAME” VARCHAR2(25 BYTE),
“EMAIL” VARCHAR2(25 BYTE),
“PHONE_NUMBER” VARCHAR2(20 BYTE),
“HIRE_DATE” DATE,
“JOB_ID” VARCHAR2(10 BYTE),
“SALARY” NUMBER(8,2),
“COMMISSION_PCT” NUMBER(2,2),
“MANAGER_ID” NUMBER(6,0),
“DEPARTMENT_ID” NUMBER(4,0)
);
REM INSERTING into EMPLOYEES
SET DEFINE OFF;
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (‘100′,’Steven’,’King’,’SKING’,’515.123.4567′,to_date(’17-GIU-87′,’DD-MON-RR’),’AD_PRES’,’24000′,null,null,’90’);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (‘101′,’Neena’,’Kochhar’,’NKOCHHAR’,’515.123.4568′,to_date(’21-SET-89′,’DD-MON-RR’),’AD_VP’,’17000′,null,’100′,’90’);
Commit;