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"