Puzzle of the Week #20:
Produce the historical highest/lowest salary report that should comply with the following requirements:
- Use Single SELECT statement only
- Only employees who was paid the highest or lowest salary in their respective department at the moment of hiring should be selected
- Show name, date of hire, department number, job title, salary table (emp) columns and two additional calculated columns/flags: min_flag and max_flag to indicate that the employee was hired with the min/max salary in their respective department as of the time of hiring.
- If two or more employees in the same department are paid the same max/min salary, only the one who was hired first should be picked for the report.
- The query should work in Oracle 11g.
Comment: Apparently, the first employee in each department automatically qualifies for both, the lowest and the highest paid employee at the time of hiring.
To submit your answer (one or more!) please start following this blog and add a comment to this post.
A correct answer (and workarounds!) will be published here in a week.
My Oracle Group on Facebook:
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Would you like to read about many more tricks and puzzles?
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.