title: How do I select the workers who make more money than their leader within their respective department in sql?
body: <p>I have the following table:</p>
  <pre><code>+---------------------------------------------------+
  | ID EMPNAME DEPARTMENT ROLE       SALARY   EXTRA   |
  | 1  brian   10         sales      2000     500     |
  | 2  jenny   10         leader     3000     100     |
  | 3  sam     20         office     1500     500     |
  | 4  cory    20         leader     1200     300     |
  | 5  tifa    30         custservice4000     1000    |
  | 6  sammy   30         leader     3000     200     |
  | 7 anne     30         sales      4500     200     |
  +---------------------------------------------------+
  </code></pre>
  <p>My job is to find the people who make more than their leader (salary + extra) within each role. I will then display the <code>id</code>, <code>empname</code>, and <code>department</code> of that person. I need a way compare the salaries + extra (extra is the bonus they make each year) between roles within each department. I have tried something like the following code but it didn't work, it wasn't calculating the highest vs the leader properly.</p>
  <pre><code>SELECT a.id, a.empname, a.department
  FROM MyTable a
  JOIN (SELECT MAX(Salary + extra) As Highest, Dept FROM MyTable GROUP BY Dept) b
  ON a.Dept = b.Dept &amp;&amp; (a.Salary + a.extra) = b.Highest
  </code></pre>
  <p>How do I display the first three columns on the criteria that they make more than their leader in their specific department? The above table should output the following below:</p>
  <pre><code>+-----------------------+
  | ID EMPNAME DEPARTMENT |
  | 3  sam     20         |
  | 5  tifa    30         |
  | 7  anne    30         |
  +-----------------------+
  </code></pre>
  <p>This is done in sql developer.</p>
  <p>EDIT: Forgot to add that some values of <code>EXTRA</code> may be <code>NULL</code>.</p>