Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation

Answer: Aggregate Records Using MIN/MAX

Explore how to apply SQL aggregate functions MIN and MAX to retrieve minimum and maximum values from data sets. Understand different query techniques to find the second highest or nth highest records using subqueries, aliases, and common table expressions.

We'll cover the following...

Solution

The solution is given below:

MySQL
/* 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 ... ...