Find max and second max salary for a employee table MySQL

0 votes

Suppose 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 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)

but i am unable to figureout how to join these 2 results for the desired result


 

Nov 4 in Others by gaurav
• 22,040 points
43 views

1 answer to this question.

0 votes

SELECT MAX(SALARY) FROM Employee WHERE SALARY < (SELECT MAX(SALARY) FROM Employee); This query will give you the desired output i.e 12000, which is the second highest salary.

answered Nov 7 by gaurav
• 22,040 points

Related Questions In Others

0 votes
0 answers

Replacing H1 text with a logo image: best method for SEO and accessibility?

i want to link my logo to ...READ MORE

Feb 14 in Others by Kichu
• 19,040 points
1,870 views
0 votes
1 answer

Replacing H1 text with a logo image: best method for SEO and accessibility?

<h1> <a href="http://stackoverflow.com"> ...READ MORE

answered Feb 21 in Others by narikkadan
• 37,660 points
398 views
0 votes
1 answer
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,890 points
538 views
0 votes
1 answer

What is an index in SQL?

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

answered Feb 3 in Database by Vaani
• 7,020 points
112 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 in Database by Neha
• 8,940 points
1,497 views
0 votes
1 answer

How to get second-highest salary employees in a table

SELECT MAX(SALARY) FROM Employee WHERE SALARY < ...READ MORE

answered Nov 7 in Others by gaurav
• 22,040 points
19 views
0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10 in Others by gaurav
• 22,040 points
274 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