Calculate max salary without using aggregator or analytical function

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
Teradata Employee

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?

Teradata Employee

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);

 

 

Senior Apprentice

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
Highlighted
Teradata Employee

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);