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 pretty useful for doing calculations in queries with group by clauses.

Let's start with the definitions from the docs:

FIELD(str,str1,str2,str3,...)

FIELD() returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

SELECT field('orange', 'banana', 'apple', 'orange');
-- Returns 3
ELT(N,str1,str2,str3,...)

ELT() returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. ELT() is the complement of FIELD().

SELECT elt(3, 'banana', 'apple', 'orange');
-- Returns 'orange'

OK, now that we understand the basics let's look at a situation where we might want to reach for these functions.

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 has 'board' and 'staff' memberships, user 102 has 'staff' and 'general' memberships, and user 103 and 104 have just 'general' memberships.

Now imagine we are given a business rule that requires us to retrieve the number of users by membership, but only counting users once by their priority membership ('board' > 'staff' > 'general'). We can achieve this by using the field() function and a subquery.

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 and more perfomant way to make this same calculation using, you guessed it, elt(). It 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 group on priority membership to get the counts.

SELECT priorityMembership, count(userId) as userCount
FROM
(SELECT
userId,
elt(min(field(userGroup, "board", "staff", "general")), "board", "staff", "general") AS priorityMembership,
min(field(userGroup, "board", "staff", "general")) AS rank
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:

  • field() returns 0 if the first argument is NULL because NULL fails equality comparison with any value.
  • elt() returns NULL if the first argument is less than 1 or greater than the number of string arguments.