Recipe7.2.Finding the MinMax Value in a Column

Recipe 7.2. Finding the Min/Max Value in a Column

Problem

You want to find the highest and lowest values in a given column. For example, you want to find the highest and lowest salaries for all employees, as well as the highest and lowest salaries for each department.

Solution

When searching for the lowest and highest salaries for all employees, simply use the functions MIN and MAX, respectively:

  1 select min(sal) as min_sal, max(sal) as max_sal 2   from emp    MIN_SAL     MAX_SAL ----------  ----------        800        5000

When searching for the lowest and highest salaries for each department, use the functions MIN and MAX with the GROUP BY clause:

 1 select deptno, min(sal) as min_sal, max(sal) as max_sal 2   from emp 3  group by deptno     DEPTNO     MIN_SAL     MAX_SAL ----------  ----------  ----------         10        1300        5000         20         800        3000         30         950        2850

Discussion

When searching for the highest or lowest values, and in cases where the whole table is the group or window, simply apply the MIN or MAX function to the column you are interested in without using the GROUP BY clause.

Remember that the MIN and MAX functions ignore NULLs, and that you can have NULL groups as well as NULL values for columns in a group. The following are examples that ultimately lead to a query using GROUP BY that returns NULL values for two groups (DEPTNO 10 and 20):

  select deptno, comm   from emp  where deptno in (10,30)  order by 1      DEPTNO       COMM  ---------- ----------          10          10          10          30        300          30        500          30          30          0          30       1300          30  select min(comm), max(comm)   from emp  MIN(COMM)   MAX(COMM) ----------  ----------          0        1300  select deptno, min(comm), max(comm)   from emp  group by deptno      DEPTNO  MIN(COMM)   MAX(COMM)  ---------- ----------  ----------          10          20          30          0        1300

Remember, as Appendix A points out, even if nothing other than aggregate functions are listed in the SELECT clause, you can still group by other columns in the table; for example:

 select min(comm), max(comm)   from emp  group by deptno  MIN(COMM)   MAX(COMM) ----------  ----------          0        1300

Here you are still grouping by DEPTNO even though it is not in the SELECT clause. Including the column you are grouping by in the SELECT clause often improves readability, but is not mandatory. It is mandatory, however, that any column in the SELECT list of a GROUP BY query also be listed in the GROUP BY clause.