CODEWITHSUNDEEP
mysql
Ravindra Kumar
Ravindra Kumar

Reputation: 1950

Find max salary and name of employee, if multiple records than print all

I want to print name and salary amount of the employee which has highest salary, till now its okay but if there are multiple records than print all. There are two table given :-

EMPLOYEE TABLE :-

enter image description here

SALARY TABLE:-
salary table

my query is: -

SELECT E.NAME, S.AMOUNT 
FROM `salary` S, 
     employee E 
WHERE S.EMPLOYEE_ID = E.ID 
  and S.AMOUNT = (SELECT max(`AMOUNT`) 
                  FROM `salary`)

is there any better way to find out the solution ?

Upvotes: 0

Views: 142

Answers (2)

ekochergin
ekochergin

Reputation: 4129

It is "with ties" functionality what you're trying to achieve. Unfortunately mySQL doesn't support that (in the docs there is nothing to add to the "LIMIT" part of the query), so you have no other option rather than looking for max salary first and filter records afterwards.

So, your solution is fine for that case.

Alternatively, if you're on version 8 and newer, you may move the subquery to the with clause

with max_sal as (
  select max(amount) ms from salary
)

SELECT E.NAME, S.AMOUNT 
FROM salary S
JOIN employee E
  ON S.EMPLOYEE_ID = E.ID
JOIN max_sal ms
  ON S.AMOUNT = ms.ms

or search for it in the join directly

SELECT E.NAME, S.AMOUNT 
  FROM salary S
  JOIN employee E
    ON S.EMPLOYEE_ID = E.ID
  JOIN (select max(amount) ms from salary) ms
    ON S.AMOUNT = ms.ms

But I'm sure it won't get you any better performance

Upvotes: 1

dmg
dmg

Reputation: 4481

I like solving them with a join:

WITH M as (select max(amount) as amount from salary)
   SELECT E.NAME, S.AMOUNT 
   FROM M JOIN SALARY USING(AMOUNT) JOIN Employee USING(Id)

but your solution is perfectly fine..

Upvotes: 0

Related Questions