Find max and second max salary for a employee table MySQL

0 votes

Assume you are given the following straightforward database table, Employee, which has the columns Employee ID and Salary:

 Employee
  Employee ID    Salary
   3            200
   4            800
   7            450

I wish to write a query select max(salary) as max_salary, 2nd_max_salary from 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)

I am not able to how to join these 2 results for the desired result. Can someone please help me with this?

Aug 26, 2022 in Database by Kithuzzz
• 38,010 points
909 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Database

0 votes
1 answer

How would I find the second largest salary from a employee table? [closed]

To find the 2nd largest salary from ...READ MORE

answered Feb 8, 2022 in Database by Vaani
• 7,020 points
564 views
0 votes
1 answer

Which SQL query is used to find Nth highest salary from a salary table

If you want to find nth Salary ...READ MORE

answered Feb 14, 2022 in Database by Vaani
• 7,020 points
2,345 views
0 votes
0 answers

How to fetch the nth highest salary from a table without using TOP and sub-query?

I was recently requested to create a ...READ MORE

Aug 25, 2022 in Database by Kithuzzz
• 38,010 points
1,098 views
0 votes
1 answer

Find values in a comma separated string in a MySQL query

You can add commas to the left and ...READ MORE

answered Sep 10, 2018 in Database by Sahiti
• 6,370 points
12,655 views
0 votes
1 answer

What do ‘Record’, ‘Field’ and ‘Table’ mean in terms of a database?

You can refer to the following definitions ...READ MORE

answered Nov 12, 2018 in Database by Sahiti
• 6,370 points
20,980 views
0 votes
1 answer
0 votes
1 answer

How to change the default value and to alter a column in sql?

Hi, You can try this: ALTER TABLE foobar_data CHANGE ...READ MORE

answered Jun 24, 2019 in Big Data Hadoop by Gitika
• 65,910 points
1,234 views
0 votes
1 answer

What is an index in SQL?

An index is used to speed up ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,020 points
436 views
0 votes
1 answer

Ordering by the order of values in a SQL IN() clause

Use MySQL's FIELD() function: SELECT name, description, ... FROM ... WHERE id ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,060 points
4,469 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP