Second Highest Salary

Medium ⏱ 10 min 47% acceptance ★★★★☆ 4.4
Return the second highest distinct salary among all employees as a single column named second_highest. If there is no second distinct salary, return NULL.

Database Schema

Use these exact table and column names in your query. Open Schema & Data under the editor to preview sample rows.

customers 5 columns
customer_idINTEGERnameTEXTcityTEXTcountryTEXTsignup_dateDATE
products 4 columns
product_idINTEGERproduct_nameTEXTcategoryTEXTpriceREAL
orders 5 columns
order_idINTEGERcustomer_idINTEGERorder_dateDATEamountREALstatusTEXT
order_items 4 columns
order_idINTEGERproduct_idINTEGERquantityINTEGERunit_priceREAL
payments 5 columns
payment_idINTEGERorder_idINTEGERmethodTEXTamountREALpaid_dateDATE
employees 6 columns
employee_idINTEGERnameTEXTdepartmentTEXTmanager_idINTEGERsalaryREALhire_dateDATE

Examples

Example 1
Input
employees table
Output
second_highest
135000
Explanation

The highest salary is 180000; the next distinct value below it is 135000.

Constraints

  • Consider distinct salary values.
  • Return NULL if it does not exist.

Topics

SubqueryAggregation

Companies

GoogleMetaMicrosoft

Hints

Hint 1

Find the MAX salary that is strictly less than the overall MAX.

Hint 2

A subquery in the WHERE clause works well.

Reveal Official Solution

Try it yourself first — you learn more from a struggle than a spoiler.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Related Problems

← Calculate Monthly Revenue Average Salary by Department →
Press Run (or Ctrl+Enter) to execute your query against the sample database.
customers5 cols
  • customer_idINTEGER
  • nameTEXT
  • cityTEXT
  • countryTEXT
  • signup_dateDATE
customer_idnamecitycountrysignup_date
101RahulDelhiIndia2023-01-12
102AmanMumbaiIndia2023-02-03
103SaraBengaluruIndia2023-02-20
products4 cols
  • product_idINTEGER
  • product_nameTEXT
  • categoryTEXT
  • priceREAL
product_idproduct_namecategoryprice
1Wireless MouseElectronics799
2Office ChairFurniture5499
3NotebookStationery149
orders5 cols
  • order_idINTEGER
  • customer_idINTEGER
  • order_dateDATE
  • amountREAL
  • statusTEXT
order_idcustomer_idorder_dateamountstatus
10011012024-01-051598Delivered
10021022024-01-115499Delivered
10031012024-02-02149Cancelled
order_items4 cols
  • order_idINTEGER
  • product_idINTEGER
  • quantityINTEGER
  • unit_priceREAL
order_idproduct_idquantityunit_price
100112799
1002215499
100435149
payments5 cols
  • payment_idINTEGER
  • order_idINTEGER
  • methodTEXT
  • amountREAL
  • paid_dateDATE
payment_idorder_idmethodamountpaid_date
50011001UPI15982024-01-05
50021002Card54992024-01-11
50031005UPI9992024-03-02
employees6 cols
  • employee_idINTEGER
  • nameTEXT
  • departmentTEXT
  • manager_idINTEGER
  • salaryREAL
  • hire_dateDATE
employee_idnamedepartmentmanager_idsalaryhire_date
1MeeraAnalyticsNULL180000
2RohitAnalytics195000
3NehaEngineering1120000
Loading expected output…