<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>

The following is slightly over-engineered, but is a good SQL pattern for "top x" queries.
```
```
SELECT 
 * 
FROM 
 scott.emp
WHERE 
 (deptno,job,sal) IN
 (SELECT 
   deptno,
   job,
   max(sal) 
  FROM 
   scott.emp
  WHERE 
   deptno = 20 
   and job = 'CLERK'
  GROUP BY 
   deptno,
   job
  )
```
```
Also note that this will work in Oracle and Postgress (i think) but not MS SQL. For something similar in MS SQL see question
SQL Query to get latest price