How to Find a List Item at a Specified Position in MySQL

In MySQL, you can use the ELT() function to return a list item at a given position in the list.

The syntax goes like this:

ELT(N,str1,str2,str3,...)

Where N is the position of the item you want to return, and str1,str2,str3,... is the list.

Example

Here’s an example:

SELECT ELT(3, 'Marge', 'Homer', 'Bart') AS 'Who is at 3?';

Result:

+--------------+
| Who is at 3? |
+--------------+
| Bart         |
+--------------+

In this case we specify that we want to return the 3rd item in the list (because the first argument is 3) . And in this case the list is 'Marge', 'Homer', 'Bart', so the 3rd item is Bart.

Numbers

Here’s an example containing numbers:

SELECT ELT(3, 9, 8, 7) AS 'The 3rd item is...';

Result:

+--------------------+
| The 3rd item is... |
+--------------------+
| 7                  |
+--------------------+

Database Example

Here’s an example where I match up the results of a database query to a list of values:

SELECT 
    GenreId, 
    ELT(GenreId, 'Rock', 'Jazz', 'Country') AS Genre 
FROM Genres;

Result:

+---------+---------+
| GenreId | Genre   |
+---------+---------+
|       1 | Rock    |
|       2 | Jazz    |
|       3 | Country |
|       4 | NULL    |
|       5 | NULL    |
|       6 | NULL    |
|       7 | NULL    |
|       8 | NULL    |
+---------+---------+

In this case there were more results than I included as parameters, therefore, those results are NULL.

The ELT() function is a complement to the FIELD() function, that allows you to find the index position of a given item in a list.