ORDER BY in MySQL

PMWD · 4 Dec 2009 at 01:41

Is it possible to do a query so it ORDERS BY one column if it has a value and another if not?

Basically I want to pull out a list in the order of column A or B.

Now column A will always have a value, B not so.

Any thoughts?

Cheers

dangerstat · 4 Dec 2009 at 07:24

PMWD said:
Is it possible to do a query so it ORDERS BY one column if it has a value and another if not?

Basically I want to pull out a list in the order of column A or B.

Now column A will always have a value, B not so.

Any thoughts?

Cheers

My thoughts are I don't really understand what your trying to do.

But here's what I *think* your trying to do.

(SELECT cols FROM TABLE WHERE A != nulll ORDER BY A) UNION
(SELECT cols FROM TABLE WHERE A=null ORDER BY B)

Haircut · 4 Dec 2009 at 10:28

You should be able to do something like ORDER BY COALESCE(columnB, columnA)

That will use the value of columnB if it is not null and columnA if B is null and then order by that.

Dj_Jestar · 4 Dec 2009 at 10:49

Does this do what you want?

Code:

ORDER BY columnA, columnB

PMWD · 4 Dec 2009 at 12:52

Cheers for the input guys, Haircut your solution was bang on.

Moredhel · 4 Dec 2009 at 19:50

dangerstat said:
My thoughts are I don't really understand what your trying to do.

But here's what I *think* your trying to do.

(SELECT cols FROM TABLE WHERE A != nulll ORDER BY A) UNION
(SELECT cols FROM TABLE WHERE A=null ORDER BY B)

When dealing with nulls you should be using IS NULL or IS NOT NULL, not = NULL or != NULL.

PMWD · 4 Dec 2009 at 23:17

Hi Moredhel, is that true for updates as well?