Trying to find a way to organize null values so they show last in table

Question

I have a simple table with null values across the

row(counter, null 3456,10,null,45,null).

I am trying to make the data look like

(counter, 3456,10,45,null,null,null).

So far I have tried

SELECT * 
FROM alpha2
ORDER BY IS NULL( Value1, Value2,Value3,Value4,Value5,Value6);

Value is the column name

I have also tried

SELECT * FROM alpha2 ORDER BY -rank DESC;


Counter Value1 Value2 Value3 Value4 Value5 Value6 <- columns
Count1  3456   10     45      null  null  null .   <-rows

`ORDER BY` doesn't order columns; it orders rows. The columns are fixed in a query. The fact that you want to do something like this suggests a flaw in your data model. — Gordon Linoff, Apr 13 '19 at 16:24
I am using MySQL Workbench, would I need to order by the rows values then? Or different query altogether? — S. Caruso, Apr 13 '19 at 16:26

score 1 · Answer 1 · edited Apr 13 '19 at 17:36

1

On MS-SQL Server, the ORDER BY ... ASC clause will always place NULL values first. On MS-SQL Server, the ORDER BY ... DESC clause will always place NULL values last.

On Oracle, the ORDER BY ... ASC clause will always place NULL values last. On Oracle, the ORDER BY ... DESC clause will always place NULL values first.

The SQL standard does not explicitly define a default sort order for Nulls. Instead, on conforming systems, Nulls can be sorted before or after all data values by using the NULLS FIRST or NULLS LAST clauses of the ORDER BY list, respectively. This is the case for Oracle RDBMS. MS-SQL Server does not implement this functionality.

Sample code for sorting nulls in MySQL: Here, Data is assumed as Column and Table as Table:

    select Data
    from Table
    order by case when Data is null then 1 else 0 end, Data

edited Apr 13 '19 at 17:36

answered Apr 13 '19 at 16:32

Gulshan Yadav

424
5
13

Thank you for the info! The code works great but is there a way to sort the integers and nulls so that the row would have all the integers first and then the null last. – S. Caruso Apr 13 '19 at 16:44
example = 1,2,3,null,null,null for each row? – S. Caruso Apr 13 '19 at 16:44
Here is perfect answer for your question Hope this help. https://stackoverflow.com/questions/2051602/mysql-orderby-a-number-nulls-last – Gulshan Yadav Apr 13 '19 at 17:11
The question is about sorting columns not rows so I don't understand this answer and the linked answer is equally irrelevant. – Joakim Danielson Apr 13 '19 at 17:25
1

Thank you for the link! – S. Caruso Apr 13 '19 at 17:33

Trying to find a way to organize null values so they show last in table

1 Answers1