Pushing a row to the end of a sort with MySQL

November 11, 2011

Sometimes you have weird requirements to meet. It’s a fact of life.

One of these requirements I came across recently was outputting a result set in alpha order, but with a specific tuple at the end, instead of where it belongs in the sort.

I decided I’d figure out how to do this in SQL instead of gross rendering or a re-sort.

Please note there are other ways to solve this that are less brittle, but this was in a fairly static data set for a one off solution.

Consider this table:

Now, pull them out in alpha order:

But what if we always want “Other” at the end?

Well, our unique indices allow us to guarantee a conditional sort on the slug (or name).

To do this, we will use the CASE statement.

Here we have two ORDER BY clauses. We first use CASE to return 1 whenever the slug is “other”, and sort on that. Then we sort the sub-groups (“other” and “non-other”) by slug.

CASE is handy for other stuff too, and can handle complex expressions.

Do you ever use CASE?

Categories: Geek
Tags: ,

Leave A Comment

Your email will not be published.