Skip to content

Instantly share code, notes, and snippets.

@JacobBennett JacobBennett/blog.MD
Last active Feb 15, 2016

What would you like to do?
How SQL orders results when using equals

The end goal was to order a query by a list of specific values for a column, but then also order those by a created_at date in ascending order and then by id in ascending order

This proved to be more of a challenge than I originally thought. The solution I came up with is as follows.

SELECT id, clientId, created_at 
FROM `claims` 
WHERE `clientId` IN ('FOO', 'BAR', 'BAZ', 'FIZZ') 
ORDER BY clientId = 'FIZZ', clientId = 'BAZ', clientId = 'BAR', clientId = 'FOO', 
created_at ASC, id ASC

Direct your attention to line 4. Looking at this, you might assume that the returned order of the results would be FIZZ, BAZ, BAR, FOO but in fact, you would be wrong.

While normally the order by statements seem to stack on top of the previous order statement, in appears that when using an = to define the order, the orders stack in reverse.

That is, the last order by statement containing the = was the first result returned. It's the little things.

published: true
# published_on: 2015-08-09
# preview: this is a test of the emergency broadcast system

This comment has been minimized.

Copy link

commented Feb 14, 2016

That's really helpful for me. ;)
Thanks, JacobBennett.


This comment has been minimized.

Copy link
Owner Author

commented Feb 15, 2016

My pleasure @staritsoft. Thanks for the read.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.