SQL - order by column if value exists, otherwise second column

Question

How to order SQL result by column name and if there is not set name, order it by email.

+----+-------+-----------------+
| ID | name  |      email      |
+----+-------+-----------------+
|  1 | John  | [email protected]  |
|  2 | ---   | [email protected] |
|  3 | ---   | [email protected] |
|  4 | Peter | [email protected] |
+----+-------+-----------------+

Result should looks like that:

John, [email protected], [email protected], Peter

Answer:

$users = $this->em->createQueryBuilder()
        ->select('a.id, coalesce(concat(a.firstName, concat(\' \', a.lastName)), a.email) as orderColumn')
        ->from('Company\User\Admin', 'a')
        ->orderBy('orderColumn','ASC')
        ->getQuery()
        ->getScalarResult();

Answer 1

What means "not set name"? Is it NULL or '---'? However, you can use CASE:

SELECT 
    CASE WHEN Name IS NULL THEN Email ELSE Name END AS User
FROM 
    dbo.TableName
ORDER BY
    CASE WHEN Name IS NULL THEN Email ELSE Name END ASC, Email ASC

Answer 2

Try this

SELECT MyName = CASE 
        WHEN NAME = '---'
            THEN Email
        ELSE NAME
        END
FROM YourTable
ORDER BY CASE 
        WHEN NAME = '---'
            THEN Email
        ELSE NAME
        END ASC