<p>Consider the Oracle <code>emp</code> table. I'd like to get the employees with the top salary with <code>department = 20</code> and <code>job = clerk</code>. Also assume that there is no "empno" column, and that the primary key involves a number of columns. You can do this with:</p>

<pre><code>select * from scott.emp
where deptno = 20 and job = 'CLERK'
and sal =  (select max(sal) from scott.emp
            where deptno = 20 and job = 'CLERK')
</code></pre>

<p>This works, but I have to duplicate the test deptno = 20 and job = 'CLERK', which I would like to avoid. Is there a more elegant way to write this, maybe using a <code>group by</code>? BTW, if this matters, I am using Oracle.</p>

[{'answer_id': 51103, 'author': 'Mark Nold', 'author_id': 4134, 'author_profile': 'https://Stackoverflow.com/users/4134', 'pm_score': 3, 'selected': True, 'text': '<p>The following is slightly over-engineered, but is a good SQL pattern for "top x" queries.</p>\n\n<pre><code>SELECT \n * \nFROM \n scott.emp\nWHERE \n (deptno,job,sal) IN\n (SELECT \n   deptno,\n   job,\n   max(sal) \n  FROM \n   scott.emp\n  WHERE \n   deptno = 20 \n   and job = \'CLERK\'\n  GROUP BY \n   deptno,\n   job\n  )\n</code></pre>\n\n<p>Also note that this will work in Oracle and Postgress (i think) but not MS SQL. For something similar in MS SQL see question <a href="https://stackoverflow.com/questions/49404/sql-query-to-get-latest-price#49424">SQL Query to get latest price</a></p>\n'}, {'answer_id': 51164, 'author': 'avernet', 'author_id': 5295, 'author_profile': 'https://Stackoverflow.com/users/5295', 'pm_score': 0, 'selected': False, 'text': '<p>That\'s great! I didn\'t know you could do a comparison of (x, y, z) with the result of a SELECT statement. This works great with Oracle.</p>\n\n<p>As a side-note for other readers, the above query is missing a "=" after "(deptno,job,sal)". Maybe the Stack Overflow formatter ate it (?).</p>\n\n<p>Again, thanks Mark.</p>\n'}, {'answer_id': 51369, 'author': 'Steve Bosman', 'author_id': 4389, 'author_profile': 'https://Stackoverflow.com/users/4389', 'pm_score': 2, 'selected': False, 'text': "<p>If I was certain of the targeted database I'd go with Mark Nold's solution, but if you ever want some dialect agnostic SQL*, try</p>\n\n<pre><code>SELECT * \nFROM scott.emp e\nWHERE e.deptno = 20 \nAND e.job = 'CLERK'\nAND e.sal = (\n  SELECT MAX(e2.sal) \n  FROM scott.emp e2\n  WHERE e.deptno = e2.deptno \n  AND e.job = e2.job\n)\n</code></pre>\n\n<p>*I believe this should work everywhere, but I don't have the environments to test it.</p>\n"}, {'answer_id': 51682, 'author': 'oglester', 'author_id': 2017, 'author_profile': 'https://Stackoverflow.com/users/2017', 'pm_score': 0, 'selected': False, 'text': '<p>In Oracle you can also use the EXISTS statement, which in some cases is faster.</p>\n\n<p>For example...\nSELECT name, number\nFROM cust \nWHERE cust IN\n  ( SELECT cust_id FROM big_table )\n AND entered > SYSDATE -1\nwould be slow.</p>\n\n<p>but\nSELECT name, number\nFROM cust <strong>c</strong>\nWHERE EXISTS\n  ( SELECT cust_id FROM big_table <strong>WHERE cust_id=c.cust_id</strong> )\n AND entered > SYSDATE -1\nwould be very fast with proper indexing.  You can also use this with multiple parameters.</p>\n'}, {'answer_id': 57562, 'author': 'NateSchneider', 'author_id': 5129, 'author_profile': 'https://Stackoverflow.com/users/5129', 'pm_score': 0, 'selected': False, 'text': '<p>There are many solutions.  You could also keep your original query layout by simply adding table aliases and joining on the column names, you would still only have DEPTNO = 20 and JOB = \'CLERK\' in the query once.</p>\n\n<pre><code>SELECT \n  * \nFROM \n  scott.emp emptbl\nWHERE\n  emptbl.DEPTNO = 20 \n  AND emptbl.JOB = \'CLERK\'\n  AND emptbl.SAL =  \n    (\n      select \n        max(salmax.SAL) \n      from \n        scott.emp salmax\n      where \n        salmax.DEPTNO = emptbl.DEPTNO\n        AND salmax.JOB = emptbl.JOB\n    )\n</code></pre>\n\n<p>It could also be noted that the key word "ALL" can be used for these types of queries which would allow you to remove the "MAX" function.</p>\n\n<pre><code>SELECT \n  * \nFROM \n  scott.emp emptbl\nWHERE\n  emptbl.DEPTNO = 20 \n  AND emptbl.JOB = \'CLERK\'\n  AND emptbl.SAL &gt;= ALL  \n    (\n      select \n        salmax.SAL\n      from \n        scott.emp salmax\n      where \n        salmax.DEPTNO = emptbl.DEPTNO\n        AND salmax.JOB = emptbl.JOB\n    )\n</code></pre>\n\n<p>I hope that helps and makes sense.</p>\n'}, {'answer_id': 63160, 'author': 'Gabor Kecskemeti', 'author_id': 6572, 'author_profile': 'https://Stackoverflow.com/users/6572', 'pm_score': 1, 'selected': False, 'text': '<p>In Oracle I\'d do it with an analytical function, so you\'d only query the emp table once :</p>\n\n<pre><code>SELECT *\n  FROM (SELECT e.*, MAX (sal) OVER () AS max_sal\n          FROM scott.emp e\n         WHERE deptno = 20 \n           AND job = \'CLERK\')\n WHERE sal = max_sal\n</code></pre>\n\n<p>It\'s simpler, easier to read and more efficient. </p>\n\n<p>If you want to modify it to list list this information for all departments, then you\'ll need to use the "PARTITION BY" clause in OVER:</p>\n\n<pre><code>SELECT *\n  FROM (SELECT e.*, MAX (sal) OVER (PARTITION BY deptno) AS max_sal\n          FROM scott.emp e\n         WHERE job = \'CLERK\')\n WHERE sal = max_sal\nORDER BY deptno\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/51092', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5295/']