You need to create a query that issues dname, ename, the string 'boss' if the employee’s salary is equal to the maximum in this department. Already there is a ready code, but it needs a little remake:

SELECT * FROM (SELECT d.dname, e.ename, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) boss FROM emp e, dept d WHERE e.deptno = d.deptno) WHERE boss = 1;

It is required to display all records where the value of "boss" will or will not appear in a separate field. But my query displays the bosses immediately. All head already broke. I hope for advice more experienced sql-programmers.

  • one
    Those. we derive all employees, if a person has the maximum salary in a department - we boss , if not the maximum - do we print an empty string or null ? I correctly understood the task? - Zufir
  • right - alex
  • Thank you all so much for the clear explanation of the task! - alex

3 answers 3

  1. We remove the condition on boss=1 . This condition leaves us with only "bosses", and we need everything.

  2. Rewriting Add a field in which the "boss" will be issued or not. For this we use CASE :

     SELECT dname, ename, case when boss=1 then 'boss' else null end as boss FROM (SELECT d.dname, e.ename, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) boss FROM emp e, dept d WHERE e.deptno = d.deptno) bs;
  • Your option is more true. Thank! - alex

1) Find the maximum salary in the department. For example:

wages - salary value, see the field in their tables.
your_table is a table in which there is a field with a salary.

 SELECT max(wages) AS MAX_wages FROM your_table

2) Find all the fields associated with this salary:

yt - abbreviation for the name of the table.
myt is an abbreviation for a table with a maximum value.

 SELECT * FROM your_table AS yt JOIN (SELECT max(wages) AS MAX_wages FROM your_table ) AS myt ON myt.MAX_wages = yt.wages

    The DENSE_RANK() function ranks employees by salary, and PARTITION BY e.deptno in your query starts numbering again for each department. Further, in the external query, the condition WHERE boss = 1 leaves only one employee with the highest salary in the department, that is, exactly what you are asking for. The number of lines in the end must match the number of departments.
    If you need to output all, then remove the WHERE boss = 1 condition WHERE boss = 1 in the external query, and for the boss marks, make an additional calculated field:

     SELECT *, decode(boss, 1, 'boss', null) boss FROM (SELECT d.dname, e.ename, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) boss FROM emp e, dept d WHERE e.deptno = d.deptno)