MySQL, SQL how to retrieve the full row containing less used value in a specific column

Question

I have a nice trivial quiz for the weekend ... quite sorry it is even hard to type down the title.

Context Let me do an example (uuid and value are strings):

Table1
---------------
uuid   | value
---------------
1      | x
1      | a
3      | z
2      | y
1      | x
1      | x
3      | b

This is the expected result

---------------
uuid   | value
---------------
1      | a
3      | z
3      | b

Because the above are the rows that contain for a given uuid the less common value (or equal) among the average values used. I am playing with GROUP BY, DISTINCT, COUNT...

Attempt ONE

SELECT uuid, COUNT(uuid) as time_used, value
FROM table1
GROUP BY uuid, value ORDER BY COUNT(*) DESC;

Attempt TWO

SELECT uuid, COUNT(uuid) as occ
FROM (
    SELECT uuid, COUNT(uuid) as occ, value
    FROM table1
    GROUP BY uuid, value ORDER BY COUNT(*)) as t
GROUP BY uuid;

Attempt THREE

SELECT uuid as occ
FROM (
    SELECT uuid, COUNT(uuid) as occ, value
    FROM table1
    GROUP BY uuid, value ORDER BY COUNT(*)) as t
GROUP BY uuid
HAVING COUNT(uuid) > 1;

But it is not the right one, I still miss a step...

http://sqlfiddle.com/#!9/593a74/3

Note I am using MySQL, few data (around 2000) but still want to be 'performant', they will grow and I would like to be compliant with the only_full_group_by statement of MySQL. Thanks.

Write a query to get the count grouped by uuid and value. Then use that as a subquery in a query that gets the rows with the minimum count grouped by uuid. See https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql for how to do the second part. — Barmar, Sep 11 '20 at 16:37
let me fix the mysql version.. come on is the worst question I ever made on stackoverflow.. too tired sorry, — Vincenzo, Sep 11 '20 at 16:50

score 1 · Answer 1 · answered Sep 11 '20 at 16:51

1

In MySQL 8+, you would use window functions:

SELECT uv.*
FROM (SELECT uuid, COUNT(*) as cnt, value,
             RANK() OVER (PARTITION BY uuid ORDER BY COUNT(*)) as seqnum
      FROM table1
      GROUP BY uuid, value 
     ) uv
WHERE seqnum = 1;

answered Sep 11 '20 at 16:51

Gordon Linoff

1,242,037
58
646
786

Unfortunately i just gave a try but 0 rows are returned. – Vincenzo Sep 11 '20 at 16:56

score 1 · Accepted Answer · answered Sep 12 '20 at 01:19

In MySQL versions prior to 8, you can generate the result you require by using a subquery to get the counts of each uuid/value combination, this is then used to get the minimum and total count value for each uuid and that result is then joined to another copy of the subquery to get the output:

SELECT c.uuid, c.value
FROM (
  SELECT uuid, MIN(cnt) AS min_cnt
  FROM (
    SELECT uuid, value, COUNT(*) AS cnt
    FROM table1
    GROUP BY uuid, value
  ) c
  GROUP BY uuid
  HAVING SUM(cnt) > 1
) m
JOIN (
  SELECT uuid, value, COUNT(*) AS cnt
  FROM table1
  GROUP BY uuid, value
) c ON c.uuid = m.uuid AND c.cnt = m.min_cnt

Output:

uuid    value
1       a
3       z
3       b

Demo on dbfiddle

MySQL, SQL how to retrieve the full row containing less used value in a specific column

2 Answers2