GBase 8a在查询排序时默认排序规则为升序排列，且将null值排在最后面；按照降序排列时，则将null值排在最前面。
如：
gbase> select * from t ;
+------+------+------+
| i | j | c |
+------+------+------+
| 1 | 11 | str1 |
| 2 | NULL | str2 |
| NULL | 3 | str3 |
| 1 | NULL | str4 |
| NULL | 11 | str5 |
+------+------+------+
5 rows in set (Elapsed: 00:00:00.00)
gbase> select * from t order by i ,j;
+------+------+------+
| i | j | c |
+------+------+------+
| 1 | 11 | str1 |
| 1 | NULL | str4 |
| 2 | NULL | str2 |
| NULL | 3 | str3 |
| NULL | 11 | str5 |
+------+------+------+
5 rows in set (Elapsed: 00:00:00.02)
gbase> select * from t order by i desc,j desc;
+------+------+------+
| i | j | c |
+------+------+------+
| NULL | 11 | str5 |
| NULL | 3 | str3 |
| 2 | NULL | str2 |
| 1 | NULL | str4 |
| 1 | 11 | str1 |
+------+------+------+
5 rows in set (Elapsed: 00:00:00.02)
那么，如果想在升序排列时将null值排在最前面，或者在降序排列时将null值排在最后面，该如何处理呢？
GBase 8a提供了nulls fisrt /nulls last 关键字，用来指定null值在排序时的显示位置。
例如，升序排列，且想将null值显示在前面时，可以写成：select * from t order by I nulls first ,j nulls first;
gbase> select * from t order by I nulls first ,j nulls first;
+------+------+------+
| i | j | c |
+------+------+------+
| NULL | 3 | str3 |
| NULL | 11 | str5 |
| 1 | NULL | str4 |
| 1 | 11 | str1 |
| 2 | NULL | str2 |
+------+------+------+
5 rows in set (Elapsed: 00:00:00.02)
同样的，如果想在降序排列时将null值排在最后面，也可以使用nulls last关键字。
gbase> select * from t order by i desc ,j desc;
+------+------+------+
| i | j | c |
+------+------+------+
| NULL | 11 | str5 |
| NULL | 3 | str3 |
| 2 | NULL | str2 |
| 1 | NULL | str4 |
| 1 | 11 | str1 |
+------+------+------+
5 rows in set (Elapsed: 00:00:00.02)
gbase> select * from t order by i desc nulls last,j desc nulls last;
+------+------+------+
| i | j | c |
+------+------+------+
| 2 | NULL | str2 |
| 1 | 11 | str1 |
| 1 | NULL | str4 |
| NULL | 11 | str5 |
| NULL | 3 | str3 |
+------+------+------+
5 rows in set (Elapsed: 00:00:00.02)
Nulls first/nulls last关键字也同样可以应用在开窗函数中。开窗函数中默认为nulls last。
例如：
gbase> select id,value,birth,row_number() over(partition by value order by birth) num from t1;
+------+--------+---------------------+-----+
| id | value | birth | num |
+------+--------+---------------------+-----+
| 4 | 888 | 2021-04-13 00:00:00 | 1 |
| 3 | 888 | NULL | 2 |
| 4 | 999 | NULL | 1 |
| 1 | 234 | NULL | 1 |
| 2 | 567 | NULL | 1 |
| 5 | 555555 | 2020-01-03 00:00:00 | 1 |
+------+--------+---------------------+-----+
6 rows in set (Elapsed: 00:00:00.10)
gbase> select id,value,birth,row_number() over(partition by value order by birth nulls last) num from t1;
+------+--------+---------------------+-----+
| id | value | birth | num |
+------+--------+---------------------+-----+
| 4 | 888 | 2021-04-13 00:00:00 | 1 |
| 3 | 888 | NULL | 2 |
| 4 | 999 | NULL | 1 |
| 1 | 234 | NULL | 1 |
| 2 | 567 | NULL | 1 |
| 5 | 555555 | 2020-01-03 00:00:00 | 1 |
+------+--------+---------------------+-----+
6 rows in set (Elapsed: 00:00:00.06)
nulls first,可以看到 null 值排在了最前面了。
gbase> select id,value,birth,row_number() over(partition by value order by birth nulls first) num from t1;
+------+--------+---------------------+-----+
| id | value | birth | num |
+------+--------+---------------------+-----+
| 3 | 888 | NULL | 1 |
| 4 | 888 | 2021-04-13 00:00:00 | 2 |
| 4 | 999 | NULL | 1 |
| 1 | 234 | NULL | 1 |
| 2 | 567 | NULL | 1 |
| 5 | 555555 | 2020-01-03 00:00:00 | 1 |
+------+--------+---------------------+-----+
6 rows in set (Elapsed: 00:00:00.07)
