The Spaghetti Refactory Established 2015

Arbitrary SQL ordering

I ran across an issue the other day where I had 4 records from the database that were being displayed in order of creation date, but I wanted them displayed in an arbitrary order - not alphabetical by name or title, or chronological, or sorted by id.

Before, I probably would have used Ruby to loop through the records and build an array by comparing ids. Now, though, I know better than to use Ruby for this type of thing, because it’s faster and better for memory to do it in SQL if possible (which I learned the hard way).

Turns out, querying using SQL looks a lot like what I would have written in Ruby anyhow:

SELECT * FROM foo ORDER BY
CASE
  WHEN id=67 THEN 1
  WHEN id=23 THEN 2
  WHEN id=1362 THEN 3
  WHEN id=24 THEN 4
END

Pretty cool! Thanks to cpjolicoeur on Github for this one.

Tags