How to move up not null alphabetically sorted values in MySQL? I have a table like this:

   |Name1 |Name2 | Name3 | Name4|
   |------+------+-------+------|
   |Zoe   |NULL  |NULL   |NULL  |
   |Julia |NULL  |NULL   |NULL  |
   |NULL  |Ashley|NULL   |NULL  |
   |NULL  |Belvet|NULL   |NULL  |
   |NULL  |Maria |NULL   |NULL  |
   |NULL  |NULL  |Mark   |NULL  |
   |NULL  |NULL  |Jane   |NULL  |
   |NULL  |NULL  |NULL   |Eve   |
   |NULL  |NULL  |NULL   |Jenny |
   |NULL  |NULL  |NULL   |Ketty |


How can I order alphabetically every single column in a way that the NULL values are pushed on the bottom, like this:

   |Name1 |Name2 | Name3 | Name4|
   |------+------+-------+------|
   |Julia |Ashley|Jane   |Eve   |
   |Zoe   |Belvet|Mark   |Jenny |
   |NULL  |Maria |NULL   |Ketty |


?
What you seem to want is something rather unusuarl (at least from my perspective), where the concept of row is no longer important, and you care only about columns, ignoring from which row its values came from. This is a bit tricky, because SQL understands columns together with rows. So you need, somehow, to deconstruct your tables and reconstruct them later on.

First thing we will do is have a different table (result of a subquery) with two columns: a row_number() and one NameX:

For every column, we will do something like:

-- First column
SELECT 
     @rownum := @rownum + 1 AS rank,
     Name1
FROM 
    t,  (SELECT @rownum := 0) r
ORDER BY
    -- 'zzzzzzz' = value larger than any realistically one
    -- in column Name1. Change if necessary.
    COALESCE(Name1, 'zzzzzzzzzz'),


... and that would result in:


rank | Name1
---: | :----
   1 | Julia
   2 | Zoe  
   3 | null 
   4 | null 
   5 | null 
   6 | null 
   7 | null 
   8 | null 
   9 | null 
  10 | null 


This is one deconstruction (to put it a name).



NOTE: @rownum := @rownum + 1 is equivalent to using ROW_NUMBER() in stanrdard SQL. See ROW_NUMBER() in MySQL. MySQL doesn't have a row_number() function, but lets you instead use variables in your queries (See MySQL 5.7 Reference Manual  /  Language Structure  /  User-Defined Variables / 9.4 User-Defined Variables)



With this in mind, we then need to do:


Repeat 4 times, for Name1 .. Name4, to get actually 4 (virtual) tables. We will have actually deconstructed the original table.
We have now 4 (virtual) tables with a common column (rank) and with the same number of rows as the original table. We JOIN them together on this common column.
We get rid of the rows that are NULL end to end by adding a WHERE NOT (Name1 IS NULL AND Name2 IS NULL AND Name3 IS NULL AND Name4 IS NULL) 


The full query will then be:

SELECT
    Name1, Name2, Name3, Name4
FROM
    (SELECT   @rownum1 := @rownum1 + 1 AS rank, Name1
    FROM      t, (SELECT @rownum1 := 0) AS r1
    ORDER BY  coalesce(Name1, 'zzzzzzzzz')
    )  c1
    JOIN
    (SELECT   @rownum2 := @rownum2 + 1 AS rank, Name2
    FROM      t, (SELECT @rownum2 := 0) AS r2
    ORDER BY  coalesce(Name2, 'zzzzzzzzz')
    )  c2
    ON c2.rank = c1.rank
    JOIN
    (SELECT   @rownum3 := @rownum3 + 1 AS rank, Name3
    FROM      t, (SELECT @rownum3 := 0) AS r3
    ORDER BY  coalesce(Name3, 'zzzzzzzzz')
    )  c3
    ON c3.rank = c1.rank
    JOIN
    (SELECT   @rownum4 := @rownum4 + 1 AS rank, Name4
    FROM      t, (SELECT @rownum4 := 0) AS r4
    ORDER BY  coalesce(Name4, 'zzzzzzzzz')
    )  c4
    ON c4.rank = c1.rank
WHERE
    NOT (Name1 IS NULL AND Name2 IS NULL AND Name3 IS NULL AND Name4 IS NULL)


And this will give you your intended result:


Name1 | Name2  | Name3 | Name4
:---- | :----- | :---- | :----
Julia | Ashley | Jane  | Eve  
Zoe   | Belvet | Mark  | Jenny
null  | Maria  | null  | Ketty


You can check everything together at dbfiddle here



If you're using MariaDB 10.2 or other database that knows of WINDOW functions, you can simplify this query to:

SELECT
    Name1, Name2, Name3, Name4
FROM
    (SELECT Name1, row_number() over (order by coalesce(Name1, 'zzzzzzzzz')) AS rank
    FROM t
    )  c1
    JOIN
    (SELECT Name2, row_number() over (order by coalesce(Name2, 'zzzzzzzzz')) AS rank
    FROM t
    )  c2
    ON c2.rank = c1.rank
    JOIN
    (SELECT Name3, row_number() over (order by coalesce(Name3, 'zzzzzzzzz')) AS rank
    FROM t
    )  c3
    ON c3.rank = c1.rank
    JOIN
    (SELECT Name4, row_number() over (order by coalesce(Name4, 'zzzzzzzzz')) AS rank
    FROM t
    )  c4
    ON c4.rank = c1.rank
WHERE
    NOT (Name1 IS NULL AND Name2 IS NULL AND Name3 IS NULL AND Name4 IS NULL)


and you will get the same result.

dbfiddle here



You can even play to your advantage the fact that MariaDB sorts NULLS LAST, and ignore the COALESCE:

SELECT
    Name1, Name2, Name3, Name4
FROM
    (SELECT Name1, row_number() over (order by Name1) AS rank
    FROM t
    )  c1
    JOIN
    (SELECT Name2, row_number() over (order by Name2) AS rank
    FROM t
    )  c2
    ON c2.rank = c1.rank
    JOIN
    (SELECT Name3, row_number() over (order by Name3) AS rank
    FROM t
    )  c3
    ON c3.rank = c1.rank
    JOIN
    (SELECT Name4, row_number() over (order by Name4) AS rank
    FROM t
    )  c4
    ON c4.rank = c1.rank
WHERE
    NOT (Name1 IS NULL AND Name2 IS NULL AND Name3 IS NULL AND Name4 IS NULL);


dbfiddle here

NOTE: The execution plans are identical, so the last two queries are expected to take about the same time to perform.