CODEWITHSUNDEEP
mysql
Orlando
Orlando

Reputation: 1316

MySQL order by two varchar columns

I'm having trouble coming up with a descriptive title, so I think a better way to express what I want is with an example. Let's say I have a table with the following:

NAME | EMAIL
John | [email protected]
Mike | [email protected]
NULL | [email protected]
Bill | [email protected]

I want to order the query so that the result will be:

NAME | EMAIL
NULL | [email protected]
Bill | [email protected]
John | [email protected]
Mike | [email protected]

If it's not clear, it should be ordered by both name and email, mixing both priorities when the NAME column has no entry for that row. So if NAME is NULL, it uses the value in EMAIL to sort that row with respect to the other rows, but if NAME is not NULL, then it uses NAME and not EMAIL to sort.

As you can guess, ORDER BY name ASC, email ASC does not do the trick. I'm not really sure how to Google this, so I turn to SO. Thanks in advance.

Upvotes: 3

Views: 169

Answers (5)

ScaisEdge
ScaisEdge

Reputation: 133360

select name, email
from my_table
ORDER BY name IS NULL DESC, NAME ASC, EMAIL ASC

Upvotes: 0

Dusan Bajic
Dusan Bajic

Reputation: 10849

SELECT * 
FROM   yourtable 
ORDER  BY CASE 
            WHEN name IS NULL THEN email 
            ELSE name 
          end, 
          email

http://sqlfiddle.com/#!9/0f48ec/2

Upvotes: 0

Vidya Patil
Vidya Patil

Reputation: 41

SELECT TOP 1000 [Name]
      ,[email]
  FROM [db1].[dbo].[emailTB] order by 
    CASE WHEN Name !='NULL'  THEN [Name] else NULL  end ,email

It worked for me!

Upvotes: 0

Unix One
Unix One

Reputation: 1181

One way would be to add a column to sort by:

SELECT
    NAME,
    EMAIL,
    IFNULL(NAME, EMAIL) AS sort_column
FROM
    mytable
ORDER BY
    sort_column

Upvotes: 2

Ullas
Ullas

Reputation: 11556

Use a CASE expression in ORDER BY.

QUERY

SELECT* FROM your_table_name
ORDER BY CASE WHEN NAME IS NULL THEN 0 ELSE 1 END, NAME, EMAIL;

Upvotes: 0

Related Questions