Dmitry Dmitry - 6 months ago 19
SQL Question

SQLite - COALESCE breaks result order

I have a SQLite table (for messages). The table has two columns for order:

created
and
sent
. I need to get result sorted by
sent
field (descent), but if there is 0, then by
created
field (also descent).

I'm using SQL-function
COALESCE
, but the order of the result is wrong.

Normal result (without
COALESCE
):

SELECT * FROM messages ORDER BY sent DESC
┌─────────────┬──────────┬────────────┬────────────┐
│ external_id │ body │ created │ sent │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ qw │ 1463793500 │ 1463793493 │ <-
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ huyak │ 1463783516 │ 1463662248 │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ tete │ 1463783516 │ 1463662248 │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ Te │ 1463783516 │ 1463662248 │
└─────────────┴──────────┴────────────┴────────────┘


Wrong result (with COALESCE):

SELECT * FROM messages ORDER BY COALESCE(sent,created)=0 DESC
┌─────────────┬──────────┬────────────┬────────────┐
│ external_id │ body │ created │ sent │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ Te │ 1463783516 │ 1463662248 │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ huyak │ 1463783516 │ 1463662248 │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ tete │ 1463783516 │ 1463662248 │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ qw │ 1463793500 │ 1463793493 │ <-
└─────────────┴──────────┴────────────┴────────────┘


I tried to remove expression
=0
, then the order is correct, but that request doesn't work correctly if
sent = 0
:

SELECT * FROM messages ORDER BY COALESCE(sent,created) DESC
┌─────────────┬──────────┬────────────┬────────────┐
│ external_id │ body │ created │ sent │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ qw │ 1463793500 │ 1463793493 │ <-
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ huyak │ 1463783516 │ 1463662248 │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ tete │ 1463783516 │ 1463662248 │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ Te │ 1463783516 │ 1463662248 │
└─────────────┴──────────┴────────────┴────────────┘
but
┌─────────────┬──────────┬────────────┬────────────┐
│ external_id │ body │ created │ sent │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ Te │ 1463783516 │ 1463662248 │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ huyak │ 1463783516 │ 1463662248 │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ tete │ 1463783516 │ 1463662248 │
├─────────────┼──────────┼────────────┼────────────┤
│ ... │ qw │ 1463793500 │ 0 │ <-
└─────────────┴──────────┴────────────┴────────────┘


Does anyone know why it's happening and how to fix it?

Answer

COALESCE handles NULLs, it won't help you here. It will always return sent to you. If you compare its result to zero you're only sorting based on whether sent is zero or not. You'll have to use a CASE

... ORDER BY CASE sent WHEN 0 THEN created ELSE sent END DESC;

If you had NULLs where there is no timestamp then you could use COALESCE without the comparison.

Comments