Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Extra Fields with SQL MIN() & GROUP BY

Tags:

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.

like image 667
Tim Lytle Avatar asked Apr 06 '09 20:04

Tim Lytle


People also ask

What does MIN () do in SQL?

The MIN() function returns the smallest value of the selected column.

How do you find the minimum value of multiple columns in SQL?

you can find a row-wise minimum like this: SELECT C1, C2, C3, ( SELECT MIN(C) FROM (VALUES (C1), (C2), (C3) AS v (C) ) AS MinC FROM T ; Basically you are arranging the values of C1 , C2 , C3 as a column and are applying a normal (column-wise) aggregate function to it to find the minimum.

Can I use min in having SQL?

SQL MIN with HAVING example You use the HAVING clause to specify the filter condition for groups. To filter the groups based on the result of the MIN function, you place the MIN function in the HAVING clause. For example, the following query retrieves the employees who have the lowest salary in each department.

How do I select both min and max in SQL?

To ask SQL Server about the minimum and maximum values in a column, we use the following syntax: SELECT MIN(column_name) FROM table_name; SELECT MAX(column_name) FROM table_name; When we use this syntax, SQL Server returns a single value.


2 Answers

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).

like image 174
Tom H Avatar answered Oct 07 '22 08:10

Tom H


SELECT  e.* FROM    employee e WHERE   e.id =         (         SELECT  id         FROM    employee ei         WHERE   ei.department = 'sales'         ORDER BY                 e.salary         LIMIT 1         ) 

To get values for each department, use:

SELECT  e.* FROM    department d LEFT JOIN         employee e ON   e.id =         (         SELECT  id         FROM    employee ei         WHERE   ei.department = d.id         ORDER BY                 e.salary         LIMIT 1         ) 

To get values only for those departments that have employees, use:

SELECT  e.* FROM    (         SELECT  DISTINCT eo.department         FROM    employee eo         ) d JOIN         employee e ON   e.id =         (         SELECT  id         FROM    employee ei         WHERE   ei.department = d.department         ORDER BY                 e.salary         LIMIT 1         ) 

Of course, having an index on (department, salary) will greatly improve all three queries.

like image 29
Quassnoi Avatar answered Oct 07 '22 09:10

Quassnoi