How SQL orders results when using equals

by JacobBennett

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.

Created 4 years ago | Updated 4 years ago


GistLog © 2020
Brought to you by the lovely humans at Tighten