MySQL Order By Date And Boolean Priority: Number To Date Weirdness

I have a table which I want to get sorted by date_created descending, but there is also a pinned column which if that is 1 the row should be at the top regardless of date. The query I’ve come up with, which does work, is the following:

SELECT * FROM my_table 
  WHERE id = SOMEID
  ORDER BY (CASE WHEN pinned = 1 THEN 3 ELSE date_created END) DESC;

What I’m wondering is why mysql thinks that the number 3 is greater than all of these dates from the year 2020. My current theory is that mysql is converting 3 to the date January 1st 3000 since if I use the value 2 then the pinned rows are not in the correct place.

My questions then are: How is 3 greater than 2020-06-22 08:59:09 and is there a better way of forming this query?

Extra question: Is there a way that I can ensure that the pinned rows are also sorted by date_created descending with respect to all other pinned rows? This is less important to being the best answer than the previous two questions.

Answer

Essentially you’re right, but for the opposite reason. It’s just a simple string comparison:

SELECT '2020-01-01' > '3';
+--------------------+
| '2020-01-01' > '3' |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.01 sec)

SELECT '2020-01-01' < '3';
+--------------------+
| '2020-01-01' < '3' |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

As an alternative, consider ORDER BY pinned = 1 DESC, date

Advertisement

Answer