SQL - ID is lost after performing MIN then GROUP BY

Question

To simplify, if I had a table called 'employee':

id INT NOT NULL PRIMARY KEY,
salary FLOAT,
department VARCHAR(255)

I want to perform and query where I retrieve the minimum salary in each department. So my query is something like this:

SELECT employee.ID, MIN(employee.salary), employee.department
FROM employee
GROUP BY employee.department

But regardless of which records are found. The ID values in the result set are renamed to 1,2,3.... up to however many records (deparments) exist in the result set.

How can I maintain the actual ID's of the employees after performing the AGGREGATE function and GROUP BY? Thanks in advance.

Answer 1

This will do the trick:

SELECT employee.department, MIN(employee.salary), employee.ID
FROM employee
GROUP BY 1

Answer 2

Your script is invalid:

SELECT employee.ID, MIN(employee.salary), employee.department
FROM employee
GROUP BY employee.department

Instead, look at this:

SELECT MIN(employee.salary), employee.department
FROM employee
GROUP BY employee.department

If you need the employee id as well, then you need to use a subquery.

Answer 3

You can't. Think about it, If a Department has 20 employees, and for that department, there are three employees that have the same minimum salary, which EmployeeId do you want the the query output to display? if it was guaranteed that there was only one employee in each dept with that lowest salary, then it can be done by selecting the specific employee records where the salary is the minimum value for each Department:

Select EmployeeID
From Employee e
Where Salary = 
      (Select Min(Salary) From EMployee
       Where DepartmentId = e.DepartmentId)

but this will return multiple records per department when more than one employee has that min salary level.

Answer 4

In modern SQL Server releases (and other reasonably powerful and modern SQL engines), SQL "Window functions" are probably the best alternatives (to be preferred over subqueries and self-joins) to do what you desire:

  SELECT ID, salary, department
  FROM employee
  WHERE 1 = ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary ASC)

This works when, if multiple employees have the same (department-minimal) salary, you want just a "random-ish" one of them (you can add criteria to the ORDER BY if you want one picked by some specific criteria); look into RANK, instead of ROW_NUMBER, if you want all.

Answer 5

I would guess you're using MySQL or SQLite, because your query is ambiguous and isn't allowed by standard SQL or most brands of RDBMS. MySQL and SQLite are more permissive, so it's your responsibility to resolve the ambiguity.

Here's my usual fix:

SELECT e1.ID, e1.salary, e1.department
FROM employee e1
LEFT OUTER JOIN employee e2 ON (e1.department = e2.department
    AND e1.salary > e2.salary)
WHERE e2.department IS NULL;

Here's another solution that gives the same result:

SELECT e1.ID, e1.salary, e1.department
FROM employee e1
JOIN (SELECT e2.department, MIN(e2.salary) AS min_salary
      FROM employee e2 GROUP BY e2.department) d
  ON (e1.salary = d.min_salary);

Both of these give multiple rows per department if there are multiple employees in the department with identical minimal salaries. You need to decide how to resolve that case, because it's not clear from your problem description.