CODEWITHSUNDEEP
sql
Choi Shun Chi
Choi Shun Chi

Reputation: 93

SQL: how to find maximum value items according a attribute

I am a beginner of SQL, having this table instructor:

ID     name   dept_name   salary
001     A       d01        1000
002     B       d02        2000
003     C       d01        3000
...

I am writing a code to find people who have highest salary in each department like:

name      dept_name
 C           d01
 B           d02

I do know how to find maximum value but I have no idea how to use it by according dept_name for all each department.

Upvotes: 1

Views: 6345

Answers (4)

Richie
Richie

Reputation: 9266

You can use the group by clause. Check this w3Schools link

SELECT NAME,DEPT_NAME,max(SALARY) FROM table_name group by DEPT_NAME

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57073

I assume it is a requirement to not include the salary in the result:

WITH INSTRUCTOR
     AS
     (
      SELECT * 
        FROM (
              VALUES ('001', 'A', 'd01', 1000),
                     ('002', 'B', 'd02', 2000), 
                     ('003', 'C', 'd01', 3000)
             ) AS T (ID, name, dept_name, salary)
     ), 
     INSTRUCTOR_DEPT_HIGHEST_SALARY
     AS 
     (
      SELECT dept_name, MAX(salary) AS highest_salary
        FROM INSTRUCTOR
       GROUP 
          BY dept_name
     )
SELECT ID, name, dept_name
  FROM INSTRUCTOR AS T
 WHERE EXISTS (
               SELECT * 
                 FROM INSTRUCTOR_DEPT_HIGHEST_SALARY AS H
                WHERE H.dept_name = T.dept_name
                      AND H.salary = T.highest_salary
              );

Upvotes: 0

Curtis
Curtis

Reputation: 103388

This will ensure that only records which are the highest salary for each department are returned to the result set.

SELECT name, dept_name, salary
FROM tbl t
WHERE NOT EXISTS(SELECT salary FROM tbl t2 WHERE t2.salary>t.salary AND t2.dept_name=t.dept_name)

Using SELECT name, MAX(salary) like other answerers have used won't work. Using MAX() will return the highest salary for each department, but the name will not necessarily be related to that salary value.

For example, SELECT MIN(salary), MAX(salary) is most likely going to pull values from different records. That's how aggregate functions work.

Upvotes: 4

triclosan
triclosan

Reputation: 5724

select name,   max(dept_name)
from tbl
group by name

Upvotes: 1

Related Questions