StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You are not filtering your query this is why you have all the employees displayed.</p> <p>This would filter the employees that earn less than the max for their dept/grade:</p> <pre><code>SELECT ename, salgrade.grade, dept.dname FROM emp, salgrade, dept WHERE emp.sal BETWEEN salgrade.losal AND salgrade.hisal AND emp.deptno = dept.deptno AND emp.sal = (SELECT MAX(sal) FROM emp emp_in, salgrade grade_in WHERE emp_in.sal BETWEEN grade_in.losal AND grande_in.hisal AND emp_in.deptno = emp.deptno AND grade_in.losal = salgrade.losal) </code></pre> <p>You will still find duplicates because for instance, two people in sales earn the max salary for grade 2 (both Martin and Ward earn 1250). Either this is acceptable or you need some other criteria to only select one of them.</p> <p>You can use the <code>row_number</code> analytic function to ensure that only one row is returned by grade/dept (note that Oracle will select arbitrarily one row when there are duplicates) :</p> <pre><code>SELECT * FROM ( SELECT ename, salgrade.grade, dept.dname, row_number() OVER (PARTITION BY dept.deptno, salgrade.grade ORDER BY emp.sal DESC) rnk FROM emp, salgrade, dept WHERE emp.sal BETWEEN salgrade.losal AND salgrade.hisal AND emp.deptno = dept.deptno ) WHERE rnk = 1; ENAME GRADE DNAME RNK ---------- ------ -------------- --- MILLER 2 ACCOUNTING 1 CLARK 4 ACCOUNTING 1 KING 5 ACCOUNTING 1 ADAMS 1 RESEARCH 1 FORD 4 RESEARCH 1 JAMES 1 SALES 1 MARTIN 2 SALES 1 ALLEN 3 SALES 1 BLAKE 4 SALES 1 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload