【Question】

Source: SQL select query using joins, group by and aggregate functions

I have two tables with the following fields:

emp_table: emp_id, emp_name

 

salary_increase: emp_id, inc_date, inc_amount

 

I am required to write a query which gives the employee details, the number of times an employee has received a salary increase, the value of the maximum increase amount and the date of that increase. Here is what I have so far:

 

SELECT e.*,count(i.inc_amount), max(i.inc_amount)

 

FROMsalary_increase AS i

 

RIGHTJOINemp_table AS e

 

ON i.emp_id=e.emp_id

 

GROUPBY e.emp_id;

 

This correctly gives all the requirements apart from the date on which the maximum increase was awarded. I have tried the following with no success:

 

SELECT e.*,count(i.inc_amount),max(inc_amount),t.inc_date

 

FROMsalary_increase AS i

 

RIGHTJOINemp_table AS e

 

ON i.emp_id=e.emp_id

 

RIGHTJOIN

 

(

 

SELECT emp_id,inc_date FROM salary_increase

 

WHERE inc_amount=max(inc_amount)GROUPBY emp_id

 

)AS t

 

ON e.emp_id=t.emp_id

 

GROUPBY e.emp_id;

 

This gives an error ‘Invalid use of group function’. Does anyone know what I’m doing wrong?

 

A solution:

You can’t do this WHERE inc_amount=max(inc_amount) in the where clause, either use HAVING or do it in the condition of join, try this instead:

SELECT

 

e.emp_id,

 

e.inc_date,

 

t.TotalInc,

 

t.MaxIncAmount

 

FROMsalary_increase AS i

 

INNERJOINemp_table ASe ON i.emp_id=e.emp_id

 

INNERJOIN

 

(

 

SELECT

 

emp_id,

 

MAX(inc_amount)AS MaxIncAmount,

 

COUNT(i.inc_amount)AS TotalInc

 

FROM salary_increase

 

GROUPBY emp_id

 

)ASt ON e.emp_id =t.emp_id AND e.inc_amount =t.MaxIncAmount;

 

【Answer】

A SQL max function gets the maximum value but can’t get the record holding the max value. In your case grouping operation and associated operation are involved, and that makes coding trickier. Other methods include keep function and top plus row_number and window functions. But all of them are inconvenient.

SPL is a better way of getting this done. The Structured Process Language’s top function gets the record having the largest value. For example, salary.top(3;amount) gets the three records holding the smallest amounts, and salary.top(-1;amount) gets the record with the largest amount value. The following SPL script can get what you want:

 

	A
1	= db.query("select * from   salary_increase")
2	=A1.group(emp_id)
3	=A2.(~.top(-1;inc_amount))
4	=A3.union()
5	=db.query("select * from   emp_table")
6	=join(A4,emp_id; A5,emp_id)

 

A1: Retrieve data from salary_increase using a SQL-like statement.

A2: Group A1’s records by emp_id.

A3: Get the record containing the largest inc_amount from each group.

A4: Union A3’s groups.

A5: Retrieve data from emp_table using a SQL-like statement.

A6: Join A4 and A5 through emp_id.

esProc is designed to processing structured data and integration-friendly with a Java program or a reporting tool. Details are explained in How to Call an SPL Script in Java.