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]

Re: Order mixed columns?

frizzle (phpfrizzle@gma il.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]

Re: Order mixed columns?

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]

Re: Order mixed columns?

On 27 Oct 2005 10:41:29 -0700, yf110@vtn1.vict oria.tc.ca (Malcolm Dew-Jones)
wrote:
[color=blue]
>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.[/color]

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 :: andy@andyh.co.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Andy Hassall]

Re: Order mixed columns?

On 27 Oct 2005 10:51:08 -0700, "frizzle" <phpfrizzle@gma il.com> wrote:
[color=blue]
>Yes, apparently you're right Malcolm.[/color]

It's customary to quote some context.
[color=blue]
>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?[/color]

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 :: andy@andyh.co.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool