MySQL order by hack

On a late afternoon I came to discuss an ordering issue on a legacy table. Not really a big issue, but I think the solution could be interesting. It is not the most recommended way of doing things, but interesting none the less.

The table

I do not know the specifics of the table, but to show what happens we only need the following:

CREATE TABLE `test_orderby` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tags` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
);

Also, we need some data:

INSERT INTO `test_orderby` VALUES ('1', 'A,B,C');
INSERT INTO `test_orderby` VALUES ('2', 'X,Y,Z,A,B');
INSERT INTO `test_orderby` VALUES ('3', 'B,D,F');
INSERT INTO `test_orderby` VALUES ('4', 'A,C,E');

The request

What we wanted to do is to search for a specific tag (B) and order the results. The records should be ordered in such a way that the matching tag in an earlier position should be positioned higher, and records without the tag should be at the bottom. So, in the case of the B the results should come in the order of 3, 1, 2, 4.

Getting the result

To find the tag in a column containing a CSV string, MySQL provides the FIND_IN_SET() function. The usage is simple enough, you pass the tag you want to find, it returns it's position when found or zero otherwise. Let's see what happens:

SELECT *, FIND_IN_SET('B', `tags`) FROM `test_orderby`

You should see the following result:

id	tags	FIND_IN_SET('B', `tags`)
1	A,B,C	2
2	X,Y,Z,A,B	5
3	B,D,F	1
4	A,C,E	0

As you can see, we can almost use the last column as our sort order. The problem lies in the fact that zero is returned when the tag is not found. Our first thought on getting around it was to use an IF():

SELECT *, FIND_IN_SET('B', `tags`) AS `sort_field` FROM `test_orderby`
ORDER BY IF(`sort_field` = 0, 99, `sort_field`)

As you can see for yourself, the results are now ordered as desired. Problem solved ? Perhaps in this case. We defined our tags column as VARCHAR(255), so technically we could have 128 tags (128 single character tags and 127 commas). Our arbitrary value of 99 would not suffice. Okay you say, lets change it to 129. However, what if someone decides to change the column to include more tags. It could be a LONGTEXT column. How would you choose your value now ? It would be nice if MySQL would provide us with a MAXINT constant, but unfortunately there isn't one. We can generate it though. Try the following query:

SELECT CAST(-1 AS UNSIGNED)

It will return 18.446.744.073.709.551.615 (separators added for readability). This is a value we can use in our IF. Chances are you'll never max out on it, not taking into account the terrible design of your table. Your new query would be:

SELECT *, FIND_IN_SET('B', `tags`) AS `sort_field` FROM `test_orderby`
ORDER BY IF(`sort_field` = 0, CAST(-1 AS UNSIGNED), `sort_field`)

The hack

Although this works, I came up with yet another solution. Since we're using a CAST() on -1, we could use the value returned by FIND_IN_SET(). We're not interested in the result and only use it to sort the records, so let's move it to the ORDER BY. To get to the -1 we need, to move our non-matching records to the bottom, we can subtract 1 from the FIND_IN_SET() result. If we do this then we can CAST() that result to UNSIGNED, leaving the (new) values from zero upwards intact (the records having a matching tag), but the -1 (zero in our original result) will change to the maximum integer value, thus moving it down. The query looks like this:

SELECT * FROM `test_orderby`
ORDER BY CAST(FIND_IN_SET('B', `tags`) - 1 AS UNSIGNED)

Try it, the result will be the same. Although this may look nice (and fun) to use, I really don't recommend it. Although it works, if left undocumented, it can be hell to maintain after a year or so. So, be careful. I just wanted to show an alternative way of creating a query. Please note that this is based on a legacy table, which could not be altered. Please make sure your own designs are better than this.