mysql - Extra Fields with SQL MIN() & GROUP BY

Question

When using the SQL MIN() function, along with GROUP BY, will any additional columns (not the MIN column, or one of the GROUP BY columns) match the data in the matching MIN row?

For example, given a table with department names, employee names, and salary:

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

Obviously I'll get two good columns, the minimum salary and the department. Will the employee name (and any other employee fields) be from the same row? Namely the row with the MIN(salary)?

I know there could very possibly be two employees with the same (and lowest) salary, but all I'm concerned with (now) is getting all the information on the (or a single) cheapest employee.

Would this select the cheapest salesman?

SELECT min(salary), e.* FROM employee e WHERE department = 'sales'

Essentially, can I be sure that the data returned along with the MIN() function will matches the (or a single) record with that minimum value?

If the database matters, I'm working with MySql.

Answer 1

If you wanted to get the "cheapest" employee in each department you would have two choices off the top of my head:

SELECT
     E.*     -- Don't actually use *, list out all of your columns
FROM
     Employees E
INNER JOIN
     (
          SELECT
               department,
               MIN(salary) AS min_salary
          FROM
               Employees
          GROUP BY
               department
     ) AS SQ ON
     SQ.department = E.department AND
     SQ.min_salary = E.salary

Or you can use:

SELECT
     E.*
FROM
     Employees E1
LEFT OUTER JOIN Employees E2 ON
     E2.department = E1.department AND
     E2.salary < E1.salary
WHERE
     E2.employee_id IS NULL -- You can use any NOT NULL column here

The second statement works by effectively saying, show me all employees where you can't find another employee in the same department with a lower salary.

In both cases, if two or more employees have equal salaries that are the minimum you will get them both (all).