Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (6.1k points)

Assume that you are given the following simple database table called Employee that has 2 columns named Employee ID and Salary:

  Employee
  Employee ID    Salary
   3            200
   4            800
   7            450

I want to write a query select max(salary) as max_salary, 2nd_max_salary from the employee.

Then it should return

  max_salary   2nd_max_salary
   800             450

I know how to find 2nd highest salary

   SELECT MAX(Salary) FROM Employee
  WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

or to find the nth:

  SELECT FROM Employee Emp1 WHERE (N-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2
  WHERE Emp2.Salary > Emp1.Salary)

Though I can't unable to figure out how can I join these two results for the desired result.

1 Answer

0 votes
by (12.7k points)

You can simply run two queries as inner queries to return two columns:

select
  (SELECT MAX(Salary) FROM Employee) maxsalary,
  (SELECT MAX(Salary) FROM Employee
  WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )) as [2nd_max_salary]

Willing to Learn SQL? Sign up for this SQL Certification course by Intellipaat now.

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jul 11, 2019 in SQL by Tech4ever (20.3k points)

Browse Categories

...