SELECT grouping by value in field

Question

Given the following (greatly simplified) example table:

CREATE TABLE `permissions` (
    `name` varchar(64) NOT NULL DEFAULT '',
    `access` enum('read_only','read_write') NOT NULL DEFAULT 'read_only'
);

And the following example contents:

| name | access     |
=====================
| foo  | read_only  |
| foo  | read_write |
| bar  | read_only  |

What I want to do is run a SELECT query that fetches one row for each unique value in name, favouring those with an access value of read_write, is there a way that this can be done? i.e- such that the results I would get are:

foo | read_write |
bar | read_only  |

I may need to add new options to the access column in future, but they will always be in order of importance (lowest to highest) so, if possible, a solution that can cope with this would be especially useful.

Also, to clarify, my actual table includes other fields than these, which is why I'm not using a unique key on the name column; there will be multiple rows by name by design to suit various criteria.

Answer 1

The solution proposed by Gordon is sufficient for the current requirements.

If we anticipate a future requirement for a priority order to be other than alphabetical string order (or by enum index value)...

As a modified version of Gordon's answer, I would be tempted to use the MySQL FIELD function and (its converse) ELT function, something like this:

SELECT p.name
     , ELT(
         MIN(
           FIELD(p.access
             ,'read_only','read_write','read_some'
           )
         )
       ,'read_only','read_write','read_some'
     ) AS access 
 FROM `permissions` p
GROUP BY p.name

If the specification is to pull the entire row, and not just the value of the access column, we could use an inline view query to find the preferred access, and a join back to the preferences table to pull the whole row...

SELECT p.*
  FROM ( -- inline view, to get the highest priority value of access
         SELECT r.name
              , MIN(FIELD(r.access,'read_only','read_write','read_some')) AS ax
           FROM `permissions` r
          GROUP BY r.name
       ) q
  JOIN `permissions` p
    ON p.name   = q.name
   AND p.access = ELT(q.ax,'read_only','read_write','read_some')

Note that this query returns not just the access with the highest priority, but can also return any columns from that row.

With the FIELD and ELT functions, we can implement any ad-hoc ordering of a list of specific, known values. Not just alphabetic ordering, or ordering by the enum index value.

That logic for "priority" can be contained within the query, and won't rely on an extra column(s) in the permissions table, or the contents of any other table(s).

To get the behavior we are looking for, just specifying a priority for access, the "list of the values" used in the FIELD function will need to match the "list of values" in the ELT function, in the same order, and the lists should include all possible values of access.

---

Reference:

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_elt

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_field

---

ADVANCED USAGE

Not that you have a requirement to do this, but considering possible future requirements... we note that...

A different order of the "list of values" will result in a different ordering of priority of access. So a variety of queries could each implement their own different rules for the "priority". Which access value to look for first, second and so on, by reordering the complete "list of values".

Beyond just reordering, it is also possible to omit a possible value from the "list of values" in the FIELD and ELT functions. Consider for example, omitting the 'read_only' value from the list on this line:

              , MIN(FIELD(r.access,'read_write','read_some')) AS ax

and from this line:

  AND p.access = ELT(q.ax,'read_write','read_some')

That will effectively limit the name rows returned. Only name that have an access value of 'read_write' or 'read_some'. Another way to look at that, a name that has only a 'read_only' for access will not be returned by the query.

Other modifications to the "list of values", where the lists don't "match" are also possible, to implement even more powerful rules. For example, we could exclude a name that has a row with 'read_only'.

For example, in the ELT function, in place of the 'read_only' value, we use a value that we know does not (and cannot) exist on any rows. To illustrate,

we can include 'read_only' as the "highest priority" on this line...

              , MIN(FIELD(r.access,'read_only','read_write','read_some')) AS ax 
                                   ^^^^^^^^^^^

so if a row with 'read_only' is found, that will take priority. But in the ELT function in the outer query, we can translate that back to a different value...

  AND p.access = ELT(q.ax,'eXcluDe','read_write','read_some')
                          ^^^^^^^^^ 

If we know that 'eXcluDe' doesn't exist in the access column, we have effectively excluded any name which has a 'read_only' row, even if there is a 'read_write' row.

Not that you have a specification or current requirement to do any of that. Something to keep in mind for future queries that do have these kinds of requirements.

Answer 2

This works too:

SELECT name, MAX(access)
  FROM permissions
GROUP BY name ORDER BY MAX(access) desc

Answer 3

You can use distinct statement (or Group by) SELECT distinct name, access FROM tab;

Answer 4

You can create another table with the priority of the access (so you can add new options), and then group by and find the MIN() value of the priority table:

E.g. create a table called Priority with the values

| PriorityID| access     |
========================
| 1         | read_write |
| 2         | read_only  |

And then,

SELECT A.Name, B.Access
FROM (
    SELECT A.name, MIN(B.PriorityID) AS Most_Valued_Option  -- This will be 1 if there is a read_write for that name
    FROM permissions A
    INNER JOIN Priority B
    ON A.Access = B.Access
    GROUP BY A.Name ) A
INNER JOIN Priority B
ON A.Most_Valued_Option = B.PriorityID   
   -- Join that ID with the actual access 
   -- (and we will select the value of the access in the select statement)

Answer 5

The following will work on your data:

select name, max(access)
from permissions
group by name;

However, this orders by the string values, not the indexes. Here is another method:

select name,
       substring_index(group_concat(access order by access desc), ',') as access
from permissions
group by name;

It is rather funky that order by goes by the index but min() and max() use the character value. Some might even call that a bug.