SQLite Forum

ORDER BY out of order. Is this a bug or expected?
Login

ORDER BY out of order. Is this a bug or expected?

(1) By anonymous on 2021-05-19 14:52:18 [link] [source]

create table t(n float);
insert into t values(-574.41),(500.00);

select printf('%8.2f',n) as n
  from t
  order by n desc;

gives

+----------+
|    n     |
+----------+
|  -574.41 |
|   500.00 |
+----------+

which indicates n is treated as string (the result of printf) instead of float (the column's affinity).

So, the question is:

Should ORDER BY see the alias of the printf that masks the original column name on the SELECT part, or the actual column name which would produce the correct result as indicated by changing the alias?

select printf('%8.2f',n) as nn
  from t
  order by n desc;

gives

+----------+
|    nn    |
+----------+
|   500.00 |
|  -574.41 |
+----------+

BTW, using a cast over printf to convert to float for ORDER BY's sake ruins the formatting so it's no-go.

(2) By Larry Brasfield (larrybr) on 2021-05-19 15:09:59 in reply to 1 [link] [source]

You have correctly surmised why the first result set is ordered as you show.

Answering your question: Your alias refers to the expression whose type is text. This is all as it should be and is documented.

(4) By anonymous on 2021-05-19 15:13:07 in reply to 2 [link] [source]

OK, thanks.

Is there any way then to get the correct order but without having to use a different name for alias as I want the result to use the original column name?

(3.1) By David Raymond (dvdraymond) on 2021-05-19 15:11:29 edited from 3.0 in reply to 1 [source]

I believe order by is supposed to look at the column aliases first, yes. I can't point you to anything specific that says it though.

Don't forget you can order by something that's not in the result columns. So you can explicitly say the column you want to order by.

sqlite> select printf('%8.2f', n) as n from t order by t.n desc;
┌──────────┐
│    n     │
├──────────┤
│   500.00 │
│  -574.41 │
└──────────┘

sqlite>

(5) By anonymous on 2021-05-19 15:13:57 in reply to 3.1 [link] [source]

OK, thanks. You just answered my 2nd question.