Assuming that NULL values sort ahead of all non-NULL values : Null « Data Type « SQL / MySQL






Assuming that NULL values sort ahead of all non-NULL values

       
mysql>
mysql> CREATE TABLE mail
    -> (
    ->  t               DATETIME,       # when message was sent
    ->  senderUser      CHAR(8),        # sender (source user and host)
    ->  senderHost      CHAR(20),
    ->  recipientUser   CHAR(8),        # recipient (destination user and host)
    ->  recipientHost   CHAR(20),
    ->  size    BIGINT,         # message size in bytes
    ->  INDEX   (t)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO mail (t,senderHost,senderUser,recipientHost,recipientUser,size)
    ->  VALUES
    ->          ('2010-05-11 10:15:08','saturn','barb','mars','tricia',58274),
    ->          ('2010-05-12 12:48:13','mars','tricia','venus','gene',194925),
    ->          ('2010-05-12 15:02:49','mars','phil','saturn','phil',1048),
    ->          ('2010-05-13 13:59:18','saturn','barb','venus','tricia',271),
    ->          ('2010-05-14 09:31:37','venus','gene','mars','barb',2291),
    ->          ('2010-05-14 11:52:17','mars','phil','saturn','tricia',5781),
    ->          ('2010-05-14 14:42:21','venus','barb','venus','barb',98151),
    ->          ('2010-05-14 17:03:01','saturn','tricia','venus','phil',2394482),
    ->          ('2010-05-15 07:17:48','mars','gene','saturn','gene',3824),
    ->          ('2010-05-15 08:50:57','venus','phil','venus','phil',978),
    ->          ('2010-05-15 10:25:52','mars','gene','saturn','tricia',998532),
    ->          ('2010-05-15 17:35:31','saturn','gene','mars','gene',3856),
    ->          ('2010-05-16 09:00:28','venus','gene','mars','barb',613),
    ->          ('2010-05-16 23:04:19','venus','phil','venus','barb',10294),
    ->          ('2010-05-17 12:49:23','mars','phil','saturn','tricia',873),
    ->          ('2010-05-19 22:21:51','saturn','gene','venus','gene',23992);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> SELECT t, senderUser, recipientUser, size
    -> FROM mail
    -> ORDER BY IF(senderUser=recipientUser,NULL,senderUser), recipientUser;
+---------------------+------------+---------------+---------+
| t                   | senderUser | recipientUser | size    |
+---------------------+------------+---------------+---------+
| 2010-05-14 14:42:21 | barb       | barb          |   98151 |
| 2010-05-19 22:21:51 | gene       | gene          |   23992 |
| 2010-05-15 17:35:31 | gene       | gene          |    3856 |
| 2010-05-15 07:17:48 | gene       | gene          |    3824 |
| 2010-05-12 15:02:49 | phil       | phil          |    1048 |
| 2010-05-15 08:50:57 | phil       | phil          |     978 |
| 2010-05-11 10:15:08 | barb       | tricia        |   58274 |
| 2010-05-13 13:59:18 | barb       | tricia        |     271 |
| 2010-05-16 09:00:28 | gene       | barb          |     613 |
| 2010-05-14 09:31:37 | gene       | barb          |    2291 |
| 2010-05-15 10:25:52 | gene       | tricia        |  998532 |
| 2010-05-16 23:04:19 | phil       | barb          |   10294 |
| 2010-05-14 11:52:17 | phil       | tricia        |    5781 |
| 2010-05-17 12:49:23 | phil       | tricia        |     873 |
| 2010-05-12 12:48:13 | tricia     | gene          |  194925 |
| 2010-05-14 17:03:01 | tricia     | phil          | 2394482 |
+---------------------+------------+---------------+---------+
16 rows in set (0.00 sec)

mysql>
mysql> drop table mail;
Query OK, 0 rows affected (0.00 sec)

   
    
    
    
    
    
    
  








Related examples in the same category

1.Retrieve NULL value
2.Concatenate string with NULL value
3.Working with NULL Values
4.Use the IS NULL and IS NOT NULL operators
5.NULL means 'not having a value'
6.Disallowing NULLs
7.Retrieve NOT NULL value
8.List NOT NULL value and order it
9.Select NOT NULL value
10.IS NOT NULL in where clause
11.NULL value in where clause
12.Read NULL value
13.Dealing With NULL Data
14.An important one to note; the result is not 0 (false), it's NULL
15.To evaluate NULL rows
16.NULL is basically a third possible result of an evaluation: There's true, false, and then there's NULL.
17.Use the IS NULL (or IS NOT NULL) comparison instead
18.Passing a NULL value to a function results in a NULL return value.
19.Set value to null
20.What is the result of the calculation 10 divided by 0?
21.If the value of an expression cannot be determined, the result is NULL: