Create a gist now

Instantly share code, notes, and snippets.

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
@staritsoft

This comment has been minimized.

Show comment Hide comment
@staritsoft

staritsoft Feb 14, 2016

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

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

@JacobBennett

This comment has been minimized.

Show comment Hide comment
@JacobBennett

JacobBennett Feb 15, 2016

My pleasure @staritsoft. Thanks for the read.

Owner

JacobBennett 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