Skip to content

Andrew Bishop

Understanding the field() and elt() Functions in MySQL

MySQL

What are the field() and elt() functions used for in MySQL? I recently came across them when implementing a voting system and found them to be pretty useful for calculating min and max values for queries with group by clauses.

Let's assume we have a database table user_membership that records users and the memberships they belong to within an organization. A user can have multiple memberships (ie. there is a one-to-many relationship between users and memberships). Here are a few example rows from our contrived database table:

iduserIdmembership
1101board
2101staff
3102staff
4102general
5103general
6104general

We can see that user 101 is a member of 'board' and 'staff', user 102 is a member of 'staff' and 'general', and user 103 and 104 have 'general' memberships.

Now let's say we have a request from an analyst to retrieve the number of users by membership, but only counting users once by their priority membership ('board' > 'staff' > 'general'). In MySQL, we can achieve this by using the field() function and a subquery. The field() function will return the index position of a value (the first argument to the function) in a list of values (the following arguments to the function).

SELECT userId, membership AS priorityMembership FROM user_membership
WHERE field(membership, "board", "staff", "general") = (
SELECT min(field(membership, "board", "staff", "general"))
FROM user_membership t
WHERE t.userId = user_membership.userId
)
GROUP BY userId;

However, there is a better way to achieve this same calculation which doesn't rely on a subquery. The elt() function allows us to convert the index value back to the corresponding string at the index number specified in the list of arguments.

SELECT
userId,
elt(min(field(membership, "board", "staff", "general")), "board", "staff", "general") AS priorityMembership,
min(field(membership, "board", "staff", "general")) AS rank
FROM user_membership
GROUP BY userId;
userIdpriorityMembershiprank
101board1
102staff2
103general3
104general3

From there, we can easily count the numbers for our analyst by grouping on priority membership.

SELECT priorityMembership, count(userId) as userCount
FROM
(SELECT
userId,
elt(min(field(membership, "board", "staff", "general")), "board", "staff", "general") AS priorityMembership,
min(field(membership, "board", "staff", "general")) AS priorityMembershipInteger
FROM user_membership
GROUP BY userId)
GROUP BY priorityMembership;
priorityMembershipuserCount
board1
staff1
general2

A couple of gotchas to note about the field() and elt() functions:

  • the indexing starts from the first argument to the function. SELECT elt(3, 'banana', 'apple', 'orange'); will return 'orange' while SELECT field('banana', 'banana', 'apple', 'orange') will return 1.
  • elt() Returns NULL if the first argument is less than 1 or greater than the number of arguments.
  • field() returns 0 if the first argument is NULL because NULL fails equality comparison with any value.