How to find minimum number that is more than specific number

Scenario:

There might be a time when there is variant number in your DB, and you want to find the number that is more than the required number. And, that number have to be the least of those group of the number that more than this number.


Data:

     Table Employee at http://sql-tips-tricks.blogspot.com/p/sample-data.html

Example:

Find the employee who have the least salary, but more than JOHN B GEYER.

Solution:

Firstly, we need to find the salary of JOHN B GEYER, and we came up with the query:

SELECT
 E.FIRST_NAME, E.LASTNAME, E.SALARY
FROM
 EMPLOYEE E
WHERE
 E.FIRST_NAME="JOHN" And E.LASTNAME="GEYER";

and we will have:

Secondly, we need to find the employees who have more salary than JOHN B GEYER, and we came up with the query:

SELECT
 E.EMP_NO, E.FIRST_NAME, E.LASTNAME
FROM
 EMPLOYEE
WHERE
 SALARY >
 (
  SELECT
   SALARY
  FROM
   EMPLOYEE E
  WHERE
   E.FIRST_NAME="JOHN" And E.LASTNAME="GEYER"
 )

and we will come up with:

Finally, we need to find the least salary in this group. The query for this is:

SELECT
 MIN(SALARY)
FROM
 (
  SELECT
   E.EMP_NO, E.FIRST_NAME, E.LASTNAME, SALARY
  FROM
   EMPLOYEE
  WHERE
   SALARY >
   (
    SELECT
     SALARY
    FROM
     EMPLOYEE E
    WHERE
     E.FIRST_NAME="JOHN" And E.LASTNAME="GEYER"
   )
    )

The result of the query is

If you want to know the information of the employee who has this salary, the query for this is

SELECT
 A.EMP_NO, A.FIRST_NAME, A.LASTNAME, A.SALARY
FROM
 EMPLOYEE A,
 (
  SELECT
   Min(X.SALARY) AS MIN_SALARY
  FROM
   (
    SELECT
     E.EMP_NO, E.FIRST_NAME, E.LASTNAME, E.SALARY
    FROM
     EMPLOYEE AS E
    WHERE
     E.SALARY>
     (
      SELECT
       SALARY
      FROM
       EMPLOYEE E
      WHERE
       E.FIRST_NAME="JOHN" And E.LASTNAME="GEYER"
     )
   ) AS X
 ) B
WHERE
 A.SALARY= B.MIN_SALARY

You will then have the result as shown below: