How to select a column not in GROUP BY clause?

In MySQL, when you try to select a column that isn’t used in the GROUP BY clause, or in an aggregate function inside the statement, it is not a valid statement according to SQL standard and will cause an error.

MySQL will show the error message:

ERROR: Expression #x of SELECT list is not in GROUP BY clause and contains nonaggregated column 'col_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Let’s demonstrate it in an example.

We have table company in MySQL:

emp_no	title	from_date	to_date
10009	Assistant Engineer	1985-02-18	1990-02-18
10013	Senior Staff	1985-10-20	9999-01-01
10048	Engineer	1985-02-24	1987-01-27
10064	Staff	1985-11-20	1992-03-02
10070	Technique Leader	1985-10-14	9999-01-01
10098	Engineer	1985-05-13	1992-05-13
10126	Staff	1985-09-08	1991-09-08
10133	Engineer	1985-12-15	1991-12-15
10137	Staff	1985-02-18	1994-02-18
10144	Staff	1985-10-14	1992-10-14

Now select the highest emp_no of each title:

SELECT
	title,
	MAX(emp_no)
FROM
	company
GROUP BY
	title;

That works just fine, and here is the result:

title	MAX(emp_no)
Assistant Engineer	10009
Senior Staff	10013
Engineer	10133
Staff	10144
Technique Leader	10070

But you also want to see the from_date value of each record. You can’t simply include from_date in the SELECT statement like this:

SELECT
	title,
	MAX(emp_no),
	from_date
FROM
	company
GROUP BY
	title;

The query fails to execute and throws back the error:

ERROR: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'company.from_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Solution: You can use ANY

SELECT
	emp_no,
	title,
	from_date
FROM
	company t1
WHERE (t1.title, t1.emp_no) = ANY (
	SELECT
		t2.title, max(t2.emp_no)
	FROM
		company t2
	GROUP BY
		t2.title);

The result is:

emp_no	title	from_date
10009	Assistant Engineer	1985-02-18
10013	Senior Staff	1985-10-20
10070	Technique Leader	1985-10-14
10133	Engineer	1985-12-15
10144	Staff	1985-10-14

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.