Finding the Min/Max Value in a Column : SQL

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

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table