CODEWITHSUNDEEP
sqloracle-databaseplsqlgroup-by
Erik
Erik

Reputation: 396

How to find minimum and maximum of a column with all other columns from the table

How to get MIN(salary) and MAX(salary) and all other columns such as name, DOB, phone number ...etc in oracle SQL query.

I have tried the following and its working fine but is there any other way using analytical functions or something like that .

SELECT
    a.*
FROM
         employees a
    JOIN (
        SELECT
            MIN(salary) min_sal,
            department_id
        FROM
            employees
        GROUP BY
            department_id
    ) b ON a.salary = min_sal
           AND a.department_id = b.department_id
UNION
SELECT
    a.*
FROM
         employees a
    JOIN (
        SELECT
            MAX(salary) max_sal,
            department_id
        FROM
            employees
        GROUP BY
            department_id
    ) b ON a.salary = max_sal
           AND a.department_id = b.department_id;

Upvotes: 0

Views: 247

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

Something like this, I presume (based on Scott's sample emp table); query returns min and max salary per each department (that's what partition by clause says):

SQL> select deptno, empno, ename, job, sal,
  2    min(sal) over (partition by deptno) min_sal,
  3    max(sal) over (partition by deptno) max_sal
  4  from emp
  5  order by deptno, sal;

    DEPTNO      EMPNO ENAME      JOB              SAL    MIN_SAL    MAX_SAL
---------- ---------- ---------- --------- ---------- ---------- ----------
        10       7934 MILLER     CLERK           1495       1495       5750
        10       7782 CLARK      MANAGER         2818       1495       5750
        10       7839 KING       PRESIDENT       5750       1495       5750
        20       7369 SMITH      CLERK            920        920       3450
        20       7876 ADAMS      CLERK           1265        920       3450
        20       7566 JONES      MANAGER         3421        920       3450
        20       7788 SCOTT      ANALYST         3450        920       3450
        20       7902 FORD       ANALYST         3450        920       3450
        30       7900 JAMES      CLERK            998        998       2993
        30       7654 MARTIN     SALESMAN        1313        998       2993
        30       7521 WARD       SALESMAN        1313        998       2993
        30       7844 TURNER     SALESMAN        1575        998       2993
        30       7499 ALLEN      SALESMAN        1680        998       2993
        30       7698 BLAKE      MANAGER         2993        998       2993

14 rows selected.

SQL>

Upvotes: 2

Related Questions