CODEWITHSUNDEEP
sql
Mike H
Mike H

Reputation: 11

query to display the highest salaries of employees by age

Can you help me? Need to get a list of the highest salaries of employee with the sample by age,first name and last name

Input:

Age        FirstName     LastName      SAL     
---------- ----------    ----------  ----------
30         Andy          Donald        175     
31         Petr          Pess          295     
30         John          Jacky         453     
31         Bob           Bobby         385     
29         Eric          Rice          957

Answer should be

Age        FirstName     LastName      SAL     
---------- ----------    ----------  ----------
31         Bob           Bobby         385     
30         John          Jacky         453     
29         Eric          Rice          957

Thanks in advance

Upvotes: 1

Views: 3673

Answers (3)

user359040
user359040

Reputation:

If you are using MySQL, the following should work:

select * from
(select * from myTable order by age desc, sal desc) sq
group by age

(Although it won't return multiple rows for employees of the same age on the same salary.)

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220887

Get all employees for which there is no employee with the same age and with a higher salary:

SELECT *
FROM employees e1
WHERE NOT EXISTS (
  SELECT 1 
  FROM employees e2
  WHERE e1.age = e2.age
  AND e1.sal < e2.sal
)
ORDER BY age DESC

If two employees have the same age and salary, both will be returned... This query will work on any database

Upvotes: 3

David Faber
David Faber

Reputation: 12485

If you have window/analytic functions available (you don't mention an RDBMS in the OP), you can do the following:

SELECT * FROM (
    SELECT Age, FirstName, LastName, SAL
         , DENSE_RANK() OVER (PARTITION BY Age ORDER BY SAL DESC) AS ranknum
      FROM employees
) WHERE ranknum = 1

This will work even when two or more employees of the same age have the same salary - both will be returned. It will also allow you to get the 2nd highest salary, etc., if you want (just change ranknum = 1 to ranknum = 2, etc.).

Edit: FYI, this will work in Oracle, SQL Server, and PostgreSQL at least.

Upvotes: 3

Related Questions