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;

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *