CODEWITHSUNDEEP
mysqlsql
BudwiseЯ
BudwiseЯ

Reputation: 1826

SQL multi-column ordering with ORDER BY

I've tried this:

SELECT first_name,last_name,company_name FROM users ORDER BY last_name, company_name ASC



I have a users table like this:

+------------------+--------------------+--------------------+
| first_name       | last_name          | company_name       |
+------------------+--------------------+--------------------+
| Alfa             | Alfa               | Bravo              |
+------------------+--------------------+--------------------+
| Echo             | Echo               | Alfa               |
+------------------+--------------------+--------------------+
| Delta            | Delta              |                    |
+------------------+--------------------+--------------------+
|                  |                    | Charlie            |
+------------------+--------------------+--------------------+

The order of the rows at the moment is:

 , ,Charlie
Alfa, Alfa, Bravo
Delta, Delta,
Echo, Echo, Alfa


What I would like to achieve, is to order rows primarily by last_name but if it's not available (empty), order by company_name but in a way that it still compares this company_name-column with the last_name-columns... Sorry for possible inaccurate explanation. Here is the order I'd like to achieve:

Alfa, Alfa, Bravo
 , ,Charlie
Delta, Delta,
Echo, Echo, Alfa

EDIT: Is this possible via a single MySQL query?

Upvotes: 2

Views: 290

Answers (5)

Benoit
Benoit

Reputation: 79185

If you use Oracle, empty VARCHAR columns are treated as NULL. Then use COALESCE or NVL as suggested above.

Else, if there is an empty string in your column (not NULL) you can use ORDER BY CASE last_name WHEN '' THEN ... etc.

Upvotes: 1

dcarneiro
dcarneiro

Reputation: 7150

You can add an extra column to the query:

SELECT first_name,last_name,company_name,
case when last_name is null 
then company_name
else last_name
end as ord 
FROM users ORDER BY ord  ASC

Upvotes: 0

Marco
Marco

Reputation: 57573

SELECT * FROM your_table
ORDER BY COALESCE(last_name, company_name)

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

You should use COALESCE:

SELECT first_name,last_name,company_name 
FROM users ORDER BY coalesce(last_name, company_name) ASC

COALESCE is ANSI SQL Compatible, BTW.

Upvotes: 6

Curtis
Curtis

Reputation: 103358

Use ISNULL() function. If the last_name value is NULL, then it'll use the company_name value for ordering instead.

ORDER BY ISNULL(last_name, company_name)

If your last_name is not NULL, and just a blank string, you can use the NULLIF() function to get around this:

ORDER BY ISNULL(NULLIF(last_name,''), company_name)

Upvotes: 1

Related Questions