CODEWITHSUNDEEP
mysqlsqlgroup-bymariadbhaving
Thomas David Baker
Thomas David Baker

Reputation: 1129

How to tell MariaDB/MySQL that a column is unique due to HAVING clause

With ONLY_FULL_GROUP_BY set (which I don't want to/can't change) the following query errors with

ERROR 1055 (42000): 'dbname.test1.person_id' isn't in GROUP BY

(or equivalent longer message in mariadb).

CREATE TABLE test1 (
    thing VARCHAR(16),
    person_id INT
);

SELECT
    thing,
    person_id
FROM
    test1
GROUP BY
    thing
HAVING
    COUNT(DISTINCT person_id) = 1

How can I "tell" MySQL that I know the person_id is unambiguous because of the HAVING clause? I don't want to use ANY_VALUE because that isn't available in all the dbs where this code needs to run.

Edit: It has been rightly pointed out that something like MAX will work on person_id here but the MAX value of a person_id doesn't really make sense and feels fragile to future changes. Is there a better way of structuring the query so that isn't necessay?

Upvotes: 0

Views: 185

Answers (2)

Thomas David Baker
Thomas David Baker

Reputation: 1129

One way to solve this is to use a subquery to find all the things that are unique to one person:

SELECT                  
    thing      
FROM          
    test1      
GROUP BY          
    thing     
HAVING         
    COUNT(DISTINCT person_id) = 1

and then grab the details we actually want in the outer query now we know which things we care about:

SELECT 
    person_id, 
    thing 
FROM 
    test1 
WHERE 
    thing IN 
        (
            SELECT                  
                thing      
            FROM          
                test1      
            GROUP BY          
                thing     
            HAVING         
                COUNT(DISTINCT person_id) = 1
        )

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65313

Just use MAX() aggregation for person_id column

SELECT
    thing,
    MAX(person_id) AS person_id
FROM
    test1
GROUP BY
    thing
HAVING
    COUNT(DISTINCT person_id) = 1

person_id is already wanted to be selected distinctly, and MAX() works for both string or numeric values. This way, only remains one non-aggregated column which conforms for each type of DBMS, including MySQL with mode of ONLY_FULL_GROUP_BY is set.

Upvotes: 4

Related Questions