aldrin27 aldrin27 - 6 months ago 20
MySQL Question

How to sort two columns in mysql

I have this items table.

SELECT * FROM items order by

order
;

Returned Data' s:

| itemID | order | description | date_ordered |
| 1 | 1 | Apple | 2016-05-26 08:00:00 |
| 2 | 1 | Banana | 2016-05-22 10:00:00 |
| 3 | 2 | Cocoa | 2016-05-29 08:00:00 |
| 4 | 3 | Lychee | 2016-05-30 12:00:00 |


Expected output:

| itemID | order | description | date_ordered |
| 1 | 1 | Apple | 2016-05-26 08:00:00 |
| 3 | 2 | Cocoa | 2016-05-29 08:00:00 |
| 4 | 3 | Lychee | 2016-05-30 12:00:00 |
| 2 | 1 | Banana | 2016-05-22 10:00:00 | <-- This is expired


I'm stuck on order by. Maybe someone can help me with this. I don't know some filters in mysql just basics.

New added items should go at the top of the query if old it must go at the bottom.

Answer

If I understand what you want, you may try this:

SELECT * FROM items ORDER BY (date_ordered < now()), `order`;

When "ordering by comparison", the result is "split" in two. Then you can order with the criteria of your choice. The comparison must be the first criterion, though.