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, 2022 in Others by gaurav
• 22,970 points
315 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, 2022 by gaurav
• 22,970 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, 2022 in Others by Kichu
• 19,050 points
2,216 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, 2022 in Others by narikkadan
• 58,640 points
1,003 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,910 points
714 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
192 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
• 8,940 points
2,278 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, 2022 in Others by gaurav
• 22,970 points
268 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, 2022 in Others by gaurav
• 22,970 points
693 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