ORDER BY problem (mySQL)

[tata668]

Is it possible to use two fields at the same time in an ORDER BY statement, using mySQL?

Let's say I have a table "user":

userId: int
userNick: varchar 255
userNickSpecial: varchar 255

Sometimes, I'd like to be able to sort the users using BOTH the "userNick" and "userNickSpecial"!

This:

SELECT * FROM user ORDER BY userNick, userNickSpecial;

will sort by userNick and then by userNickSpecial. I'd like the sort to be done on both fields at the same time!

Ex:
-----
userId: 1
userNick: 'bbb'
userNickSpecial: ''
-----
userId: 2
userNick: ''
userNickSpecial: 'ccc'
-----
userId: 3
userNick: ''
userNickSpecial: 'aaa'
-----
userId: 4
userNick: 'ddd'
userNickSpecial: ''
-----

Would sort the users like this:
userId: 3
userId: 1
userId: 2
userId: 4

Is it possible?

[Blackie]

Yes, just the way you describe it...
First it gets sorted by first column then by second.

[tata668]

Using

SELECT * FROM user ORDER BY userNick, userNickSpecial; 

Mysql returns:
---------
userId: 3 (aaa)
---------
userId: 2 (ccc)
---------
userId: 1 (bbb)
---------
userId: 4 (ddd)
---------

This i not the order I'm looking for!

[mcavic]

Try:

SELECT * FROM user
ORDER BY CONCAT(userNick, userNickSpecial);

But beware, if you have a lot of rows, this might be intensive. In that case, it might be worthwhile to create a new field with the desired data, which you can index on.

[edited by: mcavic at 11:04 pm (utc) on June 7, 2005]

[tata668]

Wow, that works mcavic!

Thanks to both of you!

[tata668]

In that case, it might be worthwhile to create a new field with the desired data, which you can index on.

The problem is my "user" example is really simplified. In fact there are 3 tables involved and one LEFT JOIN. I can't create a new field representing the "concatenation".

ORDER BY CONCAT is really what I was looking for! Thanks again.

[tata668]

Damnit!
I spoke too fast...

It works for the simplified "user" example but it doesn't work in my real code.

Problem is, I think, my LEFT JOIN returns NULL for some fields and I guess CONCAT(NULL, 'something else') doesn't equal CONCAT('', 'something else')!

My real example, using ORDER BY CONCAT, returns that:

id ¦ nick ¦ nickSpecial

834 NULL test123
836 NULL test1234
837 NULL aaaaa
835 julian ''
832 roger ''
833 roger ''

Any idea to make that CONCAT works even with NULL values?

[tata668]

Yes! I found a solution!

ORDER BY CONCAT( IFNULL( nick, '' ) , nickSpecial )

IFNULL doc: [dev.mysql.com...]

[electricocean]

Hi.... how do you get those "?" things?

electricocean

[tata668]

Hi.... how do you get those "?" things?

I don't understand what you're talking about..

[electricocean]

hi....

you wrote"

d � nick � nickSpecial

but now i realize it's just a font...

[tata668]

Is ¦ (dec #:124, hexa #: 7C) a non standard char?
I didn't know.. Sorry about that! On my french keyboard it's on the same key as "#"...

Isn't it the "pipe" char?

[electricocean]

i don't know... it's cool though

[mcavic]

Your pipe character was correct.