mysql (mariadb) ordering by two columns does not work

Question

I'm trying to get answers with ordering by text and timestamp, firs must be answers with text = null, next with text not null ordered by ts, but it doesn't work:

SELECT * FROM answers WHERE user_id = 1279942 ORDER BY text, ts desc;
+----------+---------+-------------+------------+-------------------------------+--------+
| id       | user_id | question_id | ts         | text                          | is_new |
+----------+---------+-------------+------------+-------------------------------+--------+
| 81187563 | 1279942 |    30918210 | 1487186969 | NULL                          |      0 |
| 81187560 | 1279942 |    30918209 | 1487117018 | NULL                          |      0 |
| 81187559 | 1279942 |    30918208 | 1487116399 | NULL                          |      0 |
| 81187557 | 1279942 |    30918205 | 1487024673 | Jg jg jg                      |      1 |
| 81187555 | 1279942 |    30918205 | 1487022656 | Jg jg jg                      |      1 |
|        7 | 1279942 |    30915697 | 1397915529 | lf                            |      0 |
| 81187535 | 1279942 |    30915687 | 1397047472 | да                            |      0 |
|        8 | 1279942 |    30915697 | 1397990298 | да все ок                     |      0 |
| 81187543 | 1279942 |    30915688 | 1397047582 | Да ничего                     |      0 |
| 81187561 | 1279942 |    30918206 | 1487186497 | Леха, все супер!              |      1 |
| 81187556 | 1279942 |    30918205 | 1487022667 | Оп оп                         |      1 |
| 81187558 | 1279942 |    30918207 | 1487186977 | Последний ответ               |      1 |
| 81187562 | 1279942 |    30917194 | 1487186733 | ТЕСТОВЫЙ ОТВЕТ                |      1 |
|     1537 | 1279942 |    30917195 | 1413380315 | фыв                           |      0 |
| 81187547 | 1279942 |    30915691 | 1397048820 | щл                            |      0 |
+----------+---------+-------------+------------+-------------------------------+--------+
15 rows in set (0.00 sec)

on the 4th row must be answer with id 81187558. Who know what can cause it? 😩

CollinD · Accepted Answer · 2017-02-15 20:27:16Z

2

Untested, but you could probably use something like this to order in the fashion you're after:

SELECT * FROM answers WHERE user_id = 1279942 
    ORDER BY ISNULL(text) DESC, ts DESC;

Note that ISNULL() returns 0 if the value is not null, and 1 if it is. Thus by sorting that in a descending order first, we place all of the records where text is NULL at the top. This creates two buckets that all share a ISNULL value. So the remainder will be sorted only by ts since ISNULL are all 0.

Your original approach sorts by text first, and then for each bucket of shared text values, it sorts those internally by ts.

edited Feb 15, 2017 at 20:27

answered Feb 15, 2017 at 20:14

CollinD

7,6032 gold badges25 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kRicha Over a year ago

Wow.. It works, but what is the difference between using isnull and just ordering by column?

Collectives™ on Stack Overflow

mysql (mariadb) ordering by two columns does not work

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related