SQL Interview Preparation/

...

/

Answer: Aggregate Records Using MIN/MAX

Answer: Aggregate Records Using MIN/MAX

Find a detailed explanation of how to find the second-highest number in a table using an SQL query.

We'll cover the following...

Solution

The solution is given below:

Press + to interact
/* The query to find the second-highest number */
SELECT MAX(Salary) AS SecondHighestSalary
FROM   Employees 
WHERE  Salary NOT IN (SELECT Max(Salary) 
                      FROM   Employees);

Explanation

The explanation of the code solution is given below:

  • Line 2: The SELECT query selects the maximum value in the Salary column using the MAX() function. We use AS to set an alias for the column.

  • Line 3: The FROM clause specifies the table name as Employees.

  • Line 4: We selected the second highest salary using the subquery in the where clause to filter. The NOT IN operator is a combination of two SQL keywords, NOT and IN. The NOT operator allows the retrieval of the records that don’t match the condition and the IN operator is used to specify a list of items from which we decide on the values to retrieve.

Recall of relevant concepts

We have covered the following concepts in this question:

  • Selective columns

  • Aliases

  • Sorting the data

  • Limiting the records ...