MySQL: Order by field, placing empty cells at end

Question

I have a MySQL table which contains a number of products. What I want to do is sort the table by one particular column (most of the values begin with numbers for example: 1st, 2nd), etc. However, since some records do not have a value for this column, when I try to sort, the table automatically puts empty rows FIRST.

I am looking for a way to sort the row ASCENDING, but only insert blank records at the end of the sorted records, if that makes sense?

Any help would be most gratefully received!

Answer 1

select * from table order by if(field = '' or field is null,1,0),field 

Answer 2

This is one of the most effective method

ASC Order

SELECT * FROM user ORDER BY name IS NULL, name ASC 

Expected Result:

+----+--------+------------+ | id |  name  | date_login | +----+--------+------------+ |  3 |  david | 2016-12-24 | |  2 |  john  | NULL       | |  4 |  zayne | 2017-03-02 | |  1 |  NULL  | 2017-03-12 | 

DESC Order

SELECT * FROM user ORDER BY name IS NULL, name DESC 

Expected Result:

+----+--------+------------+ | id |  name  | date_login | +----+--------+------------+ |  4 |  zayne | 2017-03-02 | |  2 |  john  | NULL       | |  3 |  david | 2016-12-24 | |  1 |  NULL  | 2017-03-12 |