MySQL Custom Sorting Rules

Today I was facing troubles with the administrative dept of the web agency where I’m working in… They wanted to have their receiptive structures sorted by a custom role…

What I first thought about was a MySQL Union query where I had to fetch all distinct types of receiptive structures (Hotels, B&Bs, Campings, etc…) in the desired order in different parametrized queries… Something like the following:

(
  SELECT
    1 as sorting_column,
    *
  FROM receiptive_structures
  WHERE type_id=4
) AS a
UNION ALL
(
  SELECT
    2 as sorting_column,
    *
  FROM receiptive_structures
  WHERE type_id=2
) AS b
UNION ALL
(
  SELECT
    3 as sorting_column,
    *
  FROM receiptive_structures
  WHERE type_id=7
) AS c
UNION ALL
(
  SELECT
    4 as sorting_column,
    *
  FROM receiptive_structures
  WHERE type_id=1
) AS d
ORDER BY sorting_column ASC

Althrough the ‘sorting_column’ shouldn’t be necessary, I had too many bad experiences with mysql and its sorting features (expecially when merging together resultsets, like in this case).

Nasty solution…
I decided to continue looking for a more elegant way of doing this…

I decided to use some kind of on-the-fly generated function in the ORDER BY clause of my MySQL Custom Sorting Query:

SELECT
((type_id=4)*1
+(type_id=2)*2
+(type_id=7)*3
+(type_id=1)*4) AS sorting_column,
r.*
FROM receiptive_structures AS r
ORDER BY sorting_column ASC

Still looks ugly…
Digging into the MySQL manual, found out that there’s a faster way of doing that!

SELECT * FROM receiptive_structures
ORDER BY FIELD(priority, 4,2,7,1);

And here’s a quick overview of the MySQL FIELD() “weight” function :)

Database changed
mysql> select id, FIELD(id,4,2,7,1) AS sorting
    -> FROM l10n ORDER BY sorting DESC limit 5;
+----+---------+
| id | sorting |
+----+---------+
|  1 |       4 |
|  7 |       3 |
|  2 |       2 |
|  4 |       1 |
|  3 |       0 |
+----+---------+
5 rows in set (0.00 sec)

I hope this will be useful for you :)

This entry was posted in DBA, Development, IT, MySQL, Tricks and tagged , , , , , . Bookmark the permalink.

One Response to MySQL Custom Sorting Rules

  1. Mat says:

    This really helped me. Thank you Angry Web Developer!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>