Skip to content Skip to sidebar Skip to footer

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 30

Solution 1:

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
WHERENOTEXISTS(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.

Solution 2:

select name,   max(dept_name)
from tbl
groupby name

Solution 3:

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
       GROUPBY dept_name
     )
SELECT ID, name, dept_name
  FROM INSTRUCTOR AS T
 WHEREEXISTS (
               SELECT*FROM INSTRUCTOR_DEPT_HIGHEST_SALARY AS H
                WHERE H.dept_name = T.dept_name
                      AND H.salary = T.highest_salary
              );

Solution 4:

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

SELECT NAME,DEPT_NAME,max(SALARY) FROM table_name groupby DEPT_NAME

Post a Comment for "Sql: How To Find Maximum Value Items According A Attribute"