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.