MySQL Query order by multiple columns

Question

I have a rows with (firstname, lastname, phone, email). What I want to make is a query which sort first by concat(firstname, lastname) asc and the rest rows, where the firstname and lastname are null have to come after the sorted by concat(firstname, lastname) ascand to be sorted depending on phone/email (if phone is null sort by email, if email is null sort by phone). Any suggestion can help me?

look for case, coalesce

souslov
– souslov

2016-07-07 12:01:49 +00:00
Commented Jul 7, 2016 at 12:01 — souslov
– souslov, Commented Jul 7, 2016 at 12:01
Note that case/coalesce require compatible data types.

jarlh
– jarlh

2016-07-07 12:04:27 +00:00
Commented Jul 7, 2016 at 12:04 — jarlh
– jarlh, Commented Jul 7, 2016 at 12:04

Gordon Linoff · Accepted Answer · 2016-07-07 12:03:53Z

3

You can put these rules into the order by. I think these are:

order by (firstname is null and lastname is null) asc,
         concat(firstname, lastname),
         coalesce(phone, email)

I am not sure if the first condition should be and or or.

answered Jul 7, 2016 at 12:03

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sushil Mate · Accepted Answer · 2016-07-07 12:09:11Z

1

select * from table order by concat(firstname, lastname) asc,  (firstname is null and lastname is null) asc, coalesce(phone, email)

answered Jul 7, 2016 at 12:09

Sushil Mate

5836 silver badges15 bronze badges

Collectives™ on Stack Overflow

MySQL Query order by multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related