MySQL Tips & Tricks: Using ORDER BY FIELD and GROUP_CONCAT()

I love databases because I always feel there is a way for retrieving my data in any shape or form I desire. Trying to find the correct syntax can sometimes be frustrating, however. Here are two tricks that have helped me during those special times:

1. Custom "ORDER BY"
   It's easy to order by ASC or DESC, but what if we want a custom order returned? This is when the FIELD() function comes in handy.
   SQL

   SELECT * FROM table_name ORDER BY FIELD(field_name, 'Small','Medium','Large');

   The disadvantage is that this will slow down your query. For maximum performance, store the field as an ENUM with the values defined in the correct order.

2. Retrieving values from multiple rows as a single comma-delimited field

   Scenario: We have multiple values spread out across rows a table, but need to retrieve them together in a single field without performing multiple queries. I used this on my old WordPress archive page--I wanted to retrieve a list of topic tags for each article, but I want to do it with the same query that selects all my articles. GROUP_CONCAT() is the answer for string concatenation.

   SQL

   SELECT GROUP_CONCAT(wp_terms.slug) AS topic, p.post_date, p.post_title, p.post_count, p.post_name, p.comment_count
   FROM wp_posts p
   LEFT JOIN wp_term_relationships r ON (r.object_id = p.ID)
   LEFT JOIN wp_term_taxonomy t ON (r.term_taxonomy_id = t.term_id)
   LEFT JOIN wp_terms ON (wp_terms.term_id = t.term_id)
   WHERE t.count > 0 AND t.taxonomy = 'category' AND p.post_status = 'publish'
   GROUP BY p.ID
   ORDER BY post_date DESC

   The "topic" column could then return values such as "php,mysql,css".