Menu
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!
375
If you sort a column with NULL values in ascending order, the NULLs will come first. Alternatively, if you add a DESC keyword to get a descending order, NULLs will appear last.
Specifying the order of NULL values In a descending sort, DESC NULLS FIRST specifies that rows with a NULL value in the sort key column precede non-NULL rows in the sorted result set. The NULLS LAST keywords instruct the database server to put NULL values last in the sorted query results.
The MySQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
Use stored procedure to produce sorted list in one text return value. Write own SQL proxy client replacing -- HIDDEN MESSAGE with ORDER BY . (I admit, this is not exactly SQL solution). Create an Indexed (Materialized) View on the table sorted by DEPARTMENT_ID that would be solely used by this query.
select * from table order by if(field = '' or field is null,1,0),field 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 | If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
