Order mixed columns?

[frizzle]

Hi all,

I have a MySQL-table. 2 fields.
Field 1 -> Name
Field 2 -> Nick

Now i want to order them alphabetically by Nick,
BUT if Nick is empty it has to pick Name.
How can i mix both of them ?
(example below)

Thanks!

Greetings Frizzle.

-- example --
unordered:
Name | Nick
John | JohnnyBoy
Dean |
George | Jaws
Chris | Ape

result:
Ape
Dean
Jaws
Johnnyboy

[Malcolm Dew-Jones]

frizzle (ph********@gmail.com) wrote:
: Hi all,

: I have a MySQL-table. 2 fields.
: Field 1 -> Name
: Field 2 -> Nick

: Now i want to order them alphabetically by Nick,
: BUT if Nick is empty it has to pick Name.
: How can i mix both of them ?
: (example below)
The oracle way would be

select *
from table
order by nvl(nick,name);

nvl returns the first argument unless it's null, in which case it returns
the second argument.

Mysql has a similar function, I think it's called ifnull(), but look it up
to be sure.
--

This programmer available for rent.

[frizzle]

Yes, apparently you're right Malcolm.
Thanks for pointing this out for me. Yet 1 question: if a user enters a
Nick,
but decides to remove it later, the field's value isn't actually NULL,
but "" if
i'm right.
What would be the solution for this?

Frizzle.

[Andy Hassall]

On 27 Oct 2005 10:41:29 -0700, yf***@vtn1.victoria.tc.ca (Malcolm Dew-Jones)
wrote:

The oracle way would be

select *
from table
order by nvl(nick,name);

nvl returns the first argument unless it's null, in which case it returns
the second argument.

The standard function is COALESCE, which for two arguments is identical to
NVL, but it accepts multiple optional arguments - the first non-null one is the
result.

mysql> select * from example;
+--------+-----------+
| name | nick |
+--------+-----------+
| John | JohnnyBoy |
| Dean | NULL |
| George | Jaws |
| Chris | Ape |
+--------+-----------+
4 rows in set (0.01 sec)

mysql> select coalesce(nick, name)
-> from example
-> order by coalesce(nick, name);
+----------------------+
| coalesce(nick, name) |
+----------------------+
| Ape |
| Dean |
| Jaws |
| JohnnyBoy |
+----------------------+
4 rows in set (0.00 sec)
--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Andy Hassall]

On 27 Oct 2005 10:51:08 -0700, "frizzle" <ph********@gmail.com> wrote:

Yes, apparently you're right Malcolm.
It's customary to quote some context.
Thanks for pointing this out for me. Yet 1 question: if a user enters a
Nick,
but decides to remove it later, the field's value isn't actually NULL,
but "" if
i'm right.
What would be the solution for this?

The correct approach would seem to be to actually set it to NULL instead of ''
if the user blanks it out, since it's a "not applicable" bit of data, rather
than the user's nickname actually being an empty string.

If you're not going to do that then you can work around in the query:

mysql> select * from example;
+--------+-----------+
| name | nick |
+--------+-----------+
| John | JohnnyBoy |
| Dean | NULL |
| George | Jaws |
| Chris | Ape |
+--------+-----------+
4 rows in set (0.01 sec)

mysql> update example set nick='' where nick is null;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from example;
+--------+-----------+
| name | nick |
+--------+-----------+
| John | JohnnyBoy |
| Dean | |
| George | Jaws |
| Chris | Ape |
+--------+-----------+
4 rows in set (0.00 sec)

mysql> select case
-> when nick is null or nick='' then name
-> else nick
-> end nick_or_name
-> from example
-> order by nick_or_name;
+--------------+
| nick_or_name |
+--------------+
| Ape |
| Dean |
| Jaws |
| JohnnyBoy |
+--------------+
4 rows in set (0.01 sec)
--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool