custom ORDER BY Clause - MySQL

Question

I have 2 columns to ORDER BY -- date, day ..

+------------------+---------------------+
| day_to_send      | date_to_send        |
+------------------+---------------------+
|     25           | 0000:00:00 00:00:00 |
|     18           | 0000:00:00 00:00:00 | 
|     11           | 0000:00:00 00:00:00 | 
|     NULL         | 2018-11-20 04:41:12 |
|     NULL         | 2019-04-11 10:50:12 |
|     NULL         | 2018-11-19 11:30:12 |
+------------------+---------------------+

Output should be

 1. 2018-11-19 11:30:12
 2. 2018-11-20 04:41:12
 3. 2019-04-11 10:50:12
 4. 11
 5. 18
 6. 25

I have tried so far, date ASC/DESC, and day ASC/DESC but i couldn't achieve this somehow.. any help would be highly appreciated.

->orderBy('date_to_send', 'ASC')
->orderBy('day_to_send', 'ASC')

I am using Laravel 4

MY OUTPUT, What I am getting

So you have 6 records you want sorted, 3 with no `day` column and 3 with no `date` column, right? — Tim Lewis, Nov 20 '18 at 15:19
I really doubt that. MySQL datetime format values are in `YYYY-MM-DD HH:MM:SS` format, not the way you have described — Madhur Bhaiya, Nov 20 '18 at 15:20
@MadhurBhaiya He could just be writing the dates in that format for purpose of asking this question. — Tim Lewis, Nov 20 '18 at 15:21
@TimLewis, I want to sort the columns first `date` coloumns should be shown in `ASC` and then day should start in `ASC` too — Syed Saqlain, Nov 20 '18 at 15:22
And 3 of them have `null` for `date`, while the other 3 have `null` for `day`? — Tim Lewis, Nov 20 '18 at 15:25
yes correct.. for `date`, they will be null or 0000:00:00 00:00:00 — Syed Saqlain, Nov 20 '18 at 15:26
Ok. One more thing, can you post the output you *are* getting? It might just be an issue where `null` is being sorted first and you are expecting it to be last, or vice-versa. — Tim Lewis, Nov 20 '18 at 15:29
i have added a screenshot of my output in my question.. @TimLewis — Syed Saqlain, Nov 20 '18 at 15:33
Then yes, it looks like it's sorting correctly. If you have `0000-00-00 00:00:00` as your `null` dates, those are technically before `2018-11-19 16:41:00`. You'll likely have to add some conditional sorting. I'd suggest working this out in your MySQL client (MySQLWorkBench, etc.) and applying to Laravel after you've figured that out. — Tim Lewis, Nov 20 '18 at 15:42
Possible duplicate of [MySQL Orderby a number, Nulls last](https://stackoverflow.com/questions/2051602/mysql-orderby-a-number-nulls-last) — Madhur Bhaiya, Nov 20 '18 at 15:58

score 0 · Answer 1 · answered Nov 21 '18 at 14:18

ok, I have found a solution to do this.. just pasting my answer if someone needed in future.. The sorting was not working properly due to datatype of my day_to_send column.

I have changed the datatype of this column from varchar to int and applied the below query and it worked..!

->orderBy('date_to_send', 'ASC')    
->orderBy('day_to_send', 'ASC')

Thanks to all those who contributed.. Cheers

custom ORDER BY Clause - MySQL

1 Answers1