SQL: aggregate function and group

Question

Consider an Oracle table emp. I would like to get employees with a higher salary using department = 20and job = clerk. Also suppose there is no empno column, and the primary key contains several columns. You can do this with:

select * from scott.emp
where deptno = 20 and job = 'CLERK'
and sal =  (select max(sal) from scott.emp
            where deptno = 20 and job = 'CLERK')

This works, but I have to duplicate the deptno = 20 and job = 'CLERK' test, which I would like to avoid. Is there a more elegant way to write this, possibly using group by? By the way, if that matters, I'm using Oracle.

Answer 1

The following is somewhat reworked, but it is a good SQL template 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
  )

, Oracle Postgress ( ), MS SQL. - MS SQL . SQL-

Answer 2

, Mark Nold, - - SQL *,

SELECT * 
FROM scott.emp e
WHERE e.deptno = 20 
AND e.job = 'CLERK'
AND e.sal = (
  SELECT MAX(e2.sal) 
  FROM scott.emp e2
  WHERE e.deptno = e2.deptno 
  AND e.job = e2.job
)

* , , .

Answer 3

Oracle , emp:

SELECT *
  FROM (SELECT e.*, MAX (sal) OVER () AS max_sal
          FROM scott.emp e
         WHERE deptno = 20 
           AND job = 'CLERK')
 WHERE sal = max_sal

, .

, "PARTITION BY" OVER:

SELECT *
  FROM (SELECT e.*, MAX (sal) OVER (PARTITION BY deptno) AS max_sal
          FROM scott.emp e
         WHERE job = 'CLERK')
 WHERE sal = max_sal
ORDER BY deptno

Answer 4

! , (x, y, z) SELECT. Oracle.

, "=" "(deptno, job, sal)". , Qaru (?).

, Mark.

Answer 5

Oracle EXISTS, .

... SELECT , FROM cust IN (SELECT cust_id FROM big_table)   > SYSDATE -1 .

SELECT , FROM cust c (SELECT cust_id FROM big_table WHERE cust_id = c.cust_id)   > SYSDATE -1 . .

Answer 6

. , , DEPTNO = 20 JOB = 'CLERK' .

SELECT 
  * 
FROM 
  scott.emp emptbl
WHERE
  emptbl.DEPTNO = 20 
  AND emptbl.JOB = 'CLERK'
  AND emptbl.SAL =  
    (
      select 
        max(salmax.SAL) 
      from 
        scott.emp salmax
      where 
        salmax.DEPTNO = emptbl.DEPTNO
        AND salmax.JOB = emptbl.JOB
    )

, "ALL" , "MAX".

SELECT 
  * 
FROM 
  scott.emp emptbl
WHERE
  emptbl.DEPTNO = 20 
  AND emptbl.JOB = 'CLERK'
  AND emptbl.SAL >= ALL  
    (
      select 
        salmax.SAL
      from 
        scott.emp salmax
      where 
        salmax.DEPTNO = emptbl.DEPTNO
        AND salmax.JOB = emptbl.JOB
    )

, .