|
|
Posted by Andy Hassall on 09/29/80 11:30
On 27 Oct 2005 10:41:29 -0700, yf110@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 :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Navigation:
[Reply to this message]
|