Database
Enthusiast

## Calculate max salary without using aggregator or analytical function

Hi ,

Do we have a method to callculate the max salary in each department without using aggregation or analytical function?

If yes, could you share me the query?

Thanks

John

4 REPLIES 4

## Re: Calculate max salary without using aggregator or analytical function

Write the table to a flat file with white-space separating the values and run a program like this awk script:

# Assuming the first value (\$1) in a line is Department,
# the second (\$2) is Salary and the third is Employee ID, etc.
\$2 > MaxSal[\$1]) { MaxSal[\$1] = \$2 }
END { for (Dept in MaxSal) print Dept, MaxSal[Dept]; }

Or do the same kind of thing in a stored procedure, using arrays.  But that's more code than I want to write, and neither of these is a query.  AND you still have to write a query to read the data, so what's the problem with using an aggregate function?

Highlighted

## Re: Calculate max salary without using aggregator or analytical function

Hi Johnsunny,

As a matter of fact, we do.

Those queries are illustrative for you to implement the logic, not real queries.

```-- GE ALL
select e1.department
, e1.salary     as max_dept_salary
from employee as e1
where e1.salary >= all (select e2.salary
from employee as e2
where e2.department = e1.department);

-- NOT EXISTS
select e1.department
, e1.salary     as max_dept_salary
from employee as e1
where not exists (select null
from employee as e2
where e2.department = e1.department
and e2.salary     > e1.salary);```

## Re: Calculate max salary without using aggregator or analytical function

Hi,

I'm impressed with @Waldar solution. I'd forgotten about the ALL qualifier.

Not sure if this applies to you, but if you've got NULL values in either salary_amount or department_number  (the 'join' columns) then you'll have to slightly modify the query to get (what I think are) correct results.

Try the following:

```-- GE ALL
select e1.department_number
, e1.salary_amount     as max_dept_salary
from employee as e1
where e1.department_number is not null
and e1.salary_amount >= all (select e2.salary_amount
from employee as e2
where e2.salary_amount is not null
and e2.department_number = e1.department_number);

-- NOT EXISTS
select e1.department_number
, e1.salary_amount     as max_dept_salary
from employee as e1
where e1.department_number is not null
and e1.salary_amount is not null
and not exists (select null
from employee as e2
where e2.department_number = e1.department_number
and e2.salary_amount is not null
and e2.salary_amount > e1.salary_amount);```

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

## Re: Calculate max salary without using aggregator or analytical function

I did some checks, those queries works even if salary / dept_no are null.

I did forget to handle the unicity of the result :

```create multiset volatile table employee, no log
( emp_pi        byteint
, emp_no        integer
, emp_name      varchar(50)
, job_title     varchar(30)
, manager_id    integer
, hire_date     date format 'yyyy-mm-dd'
, salary        decimal(18,2)
, commission    decimal(18,2)
, dept_no       integer
)
primary index (emp_pi)
on commit preserve rows;

insert into employee values (1, 1000245, 'PRADEEP', 'PRESIDENT'     ,    null, date '1981-11-17', 5000, null,  100);
insert into employee values (1, 1000258, 'BLAKE'  , 'SENIOR MANAGER', 1000245, date '1981-05-01', 2850, null,  300);
insert into employee values (1, 1000262, 'CLARK'  , 'MANAGER'       , 1000245, date '1981-06-09', 2450, null,  100);
insert into employee values (1, 1000276, 'JONES'  , 'MANAGER'       , 1000245, date '1981-04-02', 2975, null,  200);
insert into employee values (1, 1000288, 'SCOTT'  , 'SYSTEM ANALYST', 1000276, date '1987-07-13', 3000, null,  200);
insert into employee values (1, 1000292, 'FORD'   , 'SYSTEM ANALYST', 1000276, date '1981-12-03', 3000, null,  200);
insert into employee values (1, 1000294, 'SMITH'  , 'LDC'           , 1000292, date '1980-12-17',  800, null,  200);
insert into employee values (1, 1000299, 'ALLEN'  , 'SALESMAN'      , 1000258, date '1981-02-20', 1600,  300,  300);
insert into employee values (1, 1000310, 'WARD'   , 'SALESMAN'      , 1000258, date '1981-02-22', 1250,  500,  300);
insert into employee values (1, 1000312, 'MARTIN' , 'SALESMAN'      , 1000258, date '1981-09-28', 1250, 1400,  300);
insert into employee values (1, 1000315, 'TURNER' , 'SALESMAN'      , 1000258, date '1981-09-08', 1500,    0,  300);
insert into employee values (1, 1000326, 'ADAMS'  , 'LDC'           , 1000288, date '1987-07-13', 1100, null,  200);
insert into employee values (1, 1000336, 'JAMES'  , 'LDC'           , 1000258, date '1981-12-03',  950, null,  300);
insert into employee values (1, 1000346, 'MILLER' , 'LDC'           , 1000262, date '1982-01-23', 1300, null,  100);
insert into employee values (1, 1000347, 'WALDAR' , 'FORUMIST'      , 1000245, date '2017-07-03', 1000, null, null);
insert into employee values (1, 1000348, 'DAVEWL' , 'FORUMIST'      , 1000245, date '2018-01-01', null, null,  500);

select unique
e1.dept_no
, e1.salary     as max_dept_salary
from employee as e1
where e1.emp_pi = 1
and coalesce(e1.salary, 0) >= all (select coalesce(e2.salary, 0)
from employee as e2
where e2.emp_pi  = 1
and e2.dept_no = e1.dept_no);

select unique
e1.dept_no
, e1.salary     as max_dept_salary
from employee as e1
where e1.emp_pi = 1
and not exists (select null
from employee as e2
where e2.emp_pi  = 1
and e2.dept_no = e1.dept_no
and e2.salary  > e1.salary);```