0
CREATE TABLE employees(id int AUTO_INCREMENT PRIMARY KEY, emp_name VARCHAR(10), dept VARCHAR(10), salary DECIMAL(8,2));

INSERT INTO employees (dept,emp_name,salary) VALUES('Engg','Sam',1000);
INSERT INTO employees (dept,emp_name,salary) VALUES('Engg','Smith',2000);
INSERT INTO employees (dept,emp_name,salary) VALUES('HR','Denis',1500);
INSERT INTO employees (dept,emp_name,salary) VALUES('HR','Danny',3000);
INSERT INTO employees (dept,emp_name,salary) VALUES('IT','David',2000);
INSERT INTO employees (dept,emp_name,salary) VALUES('IT','John',3000);
INSERT INTO employees (dept,emp_name,salary) VALUES('Sales','Ravi',4000);
INSERT INTO employees (dept,emp_name,salary) VALUES('Sales','Bob',6000);
INSERT INTO employees (dept,emp_name,salary) VALUES('HR','Rick',4500);
INSERT INTO employees (dept,emp_name,salary) VALUES('IT','Mathew',2500);

Table: Employees

+----+----------+-------+---------+
| id | emp_name | dept  | salary  |
+----+----------+-------+---------+
|  1 | Sam      | Engg  | 1000.00 |
|  2 | Smith    | Engg  | 2000.00 |
|  3 | Denis    | HR    | 1500.00 |
|  4 | Danny    | HR    | 3000.00 |
|  5 | David    | IT    | 2000.00 |
|  6 | John     | IT    | 3000.00 |
|  7 | Ravi     | Sales | 4000.00 |
|  8 | Bob      | Sales | 6000.00 |
|  9 | Rick     | HR    | 4500.00 |
| 10 | Mathew   | IT    | 2500.00 |
+----+----------+-------+---------+

Question:

Find the Employee who earns a maximum salary in each department

Output: 

+----+----------+-------+---------+
| id | emp_name | dept  | salary  |
+----+----------+-------+---------+
|  8 | Bob      | Sales | 6000.00 |
|  9 | Rick     | HR    | 4500.00 |
|  6 | John     | IT    | 3000.00 |
|  2 | Smith    | Engg  | 2000.00 |
+----+----------+-------+---------+

When I try, I'm getting a lot of errors. Why the error shows up?

SELECT id, emp_name, salary, dept 
FROM employees 
GROUP BY dept;

Expected Result:

+----+----------+-------+---------+
| id | emp_name | dept  | salary  |
+----+----------+-------+---------+
|  8 | Bob      | Sales | 6000.00 |
|  9 | Rick     | HR    | 4500.00 |
|  6 | John     | IT    | 3000.00 |
|  2 | Smith    | Engg  | 2000.00 |
+----+----------+-------+---------+

Actual Result:

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_db.employees.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

John Conde
  • 217,595
  • 99
  • 455
  • 496
Manikandan
  • 11
  • 1
  • looks like assignment question – axnet Jan 30 '19 at 13:45
  • Please do a basic search for the error message before you post a new question. In this instance, a search for **expression of select list not in group by clause** turns up several potential duplicates that may have allowed you to figure this out yourself. – Ken White Jan 30 '19 at 13:47
  • Ignore the first two answers and instead use the third answer by Michael Berkowski. If you were using MySQL 8+ you would even have other options available. – Tim Biegeleisen Jan 30 '19 at 13:48

1 Answers1

0

You should use the following query:

SELECT A.*
FROM EMPLOYEES A
JOIN (
  SELECT DEPT, MAX(SALARY) SALARY
  FROM EMPLOYEES
  GROUP BY DEPT
) B ON A.SALARY = B.SALARY AND A.DEPT = B.DEPT
Strawberry
  • 33,750
  • 13
  • 40
  • 57
João Santos
  • 43
  • 6