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".

Comments

How to sort names if stored as first, middle and last name.

While this was a great trick for custom sorting, but I would like to ask if I can sort(order) name of a person, if it has been saved as three parts - first name, middle name and last name.
I want to sort the name by combination of first, middle and last name in both ascending and descending order.
I would really appreciate your help!

Excellent Piece Of Work

This is excellent example, i was stuck more then 2 weks in order clouse, but atlast i found a solution. FIELD() finally works for me

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
  • You can enable syntax highlighting of source code with the following tags: [code], [blockcode], [bash], [css], [html], [ini], [javascript], [mysql], [php], [sql], [xml]. PHP source code can also be enclosed in <?php ... ?> or <% ... %>.

More information about formatting options