CODEWITHSUNDEEP
mysqlsorting
Lawrence Gadette
Lawrence Gadette

Reputation: 75

MYSQL - Sort 2 Columns Alphabetically, using second column if first column is null

I am trying to sort my mysql database referencing two columns alphabetically where company_name is the primary sort but if there is no company_name then sort by last_name. I will list an example below.

company_name: (primary sort column)
1 ABC Trucking
2 Genius Heating
3
4 Xtreme Windows

last_name: (secondary column)
1 Bryant
2 Rogers
3 Flint
4 Lewis

Sorts like this:
1 ABC Trucking
3 Flint
2 Genius Heating
4 Xtreme Windows

Is this even possible or should i just merge these into an array and sort them using php?

Upvotes: 5

Views: 2850

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53840

Borrowing from user606723's answer, but showing how this is more commonly implemented in MySQL, which is to use IFNULL().

IFNULL() returns the first expression, except when the first expression is null, it will return the second expression. It's designed for these types of scenarios.

SELECT
  IFNULL(company_name, last_name) AS name
FROM mytable
ORDER BY name

Edit

If company_name holds blanks instead of NULLs, then you can do this:

SELECT
  IF(company_name <> '', company_name, last_name) AS name
FROM mytable
ORDER BY name

With an IF statement, if the first expression evaluates to true, then it returns the second expression, otherwise, it returns the third expression.

If you might have NULL or blank, then this will work:

SELECT
  IF(company_name IS NOT NULL AND company_name <> '', company_name, last_name) AS name
FROM mytable
ORDER BY name

Upvotes: 1

user606723
user606723

Reputation: 5125

Just make select with a column as 'name' using a case and order by it. Best shown by example.

SELECT
CASE WHEN company_name IS NULL THEN last_name ELSE company_name END AS name
FROM mytable
ORDER BY name ASC

If you really wanted to, you can also used the case statement right in the order by clause.
I think the above is really a better solution to your problem because you no longer even have the logic in the php, but you may disagree.

SELECT company_name, last_name
FROM mytable
ORDER BY CASE WHEN company_name IS NULL THEN last_name ELSE company_name END ASC

EDIT: I only assumed company_name IS NULL is correct for you case. If you need to do this on a different condition then thats you're own exercise. Is it null? or should be comparing it to '' instead?

Upvotes: 6

Related Questions