Recipe11.6.Finding Records with the Highest and Lowest Values

Recipe 11.6. Finding Records with the Highest and Lowest Values

Problem

You want to find "extreme" values in your table. For example, you want to find the employees with the highest and lowest salaries in table EMP.

Solution

DB2, Oracle, and SQL Server

Use the window functions MIN OVER and MAX OVER to find the lowest and highest salaries, respectively:

 1  select ename 2    from ( 3  select ename, sal, 4         min(sal)over( ) min_sal, 5         max(sal)over( ) max_sal 6    from emp 7         ) x 8   where sal in (min_sal,max_sal) 

MySQL and PostgreSQL

Write two subqueries, one each to return the MIN and MAX values of SAL:

 1  select ename 2    from emp 3   where sal in ( (select min(sal) from emp), 4                  (select max(sal) from emp) ) 

Discussion

DB2, Oracle, and SQL Server

The window functions MIN OVER and MAX OVER allow each row to have access to the lowest and highest salaries. The result set from inline view X is as follows:

  select ename, sal,        min(sal)over( ) min_sal,        max(sal)over( ) max_sal   from emp ENAME      SAL    MIN_SAL    MAX_SAL ------- ------ ---------- ---------- SMITH      800        800       5000 ALLEN     1600        800       5000 WARD      1250        800       5000 JONES     2975        800       5000 MARTIN    1250        800       5000 BLAKE     2850        800       5000 CLARK     2450        800       5000 SCOTT     3000        800       5000 KING      5000        800       5000 TURNER    1500        800       5000 ADAMS     1100        800       5000 JAMES      950        800       5000 FORD      3000        800       5000 MILLER    1300        800       5000 

Given this result set, all that's left is to return rows where SAL equals MIN_SAL or MAX_SAL.

MySQL and PostgreSQL

This solution uses two subqueries in one IN list to find the lowest and highest salaries from EMP. The rows returned by the outer query are the ones having salaries that match the values returned by either subquery.