access 遇到空值的排序

[poily]

有表a,
id sort(int)
1 1
2 2
3 null
4 3
5 null
因为默认空值为最小，所以直接order by sort 空值排在最前，而需要的排序结果是
id sort
1 1
2 2
4 3
3 null
4 null
空值排最后，其它按sort大小排序，
请问该如何写sql语句？

[水族杰纶]

select * from tb order by case when b is not null then 0 else 1 end

[ljhcy99]

SELECT *,0 AS CNT FROM TABLE
WHERE SORT <> NULL
UNION
SELECT *,1 AS CNT FROM TABLE
WHERE SORT IS NULL
ORDER BY CNT,SORT

[百年树人]

order by case when sort is null then 999999 else  sort end 

[poily]

[Quote=引用 1 楼 wufeng4552 的回复:]
SQL codeselect * from tb order by case when b is not null then 0 else 1 end
[/Quote]

access数据库，如下错误：
IErrorInfo.GetDescription 因 E_FAIL(0x80004005) 而失败。

[poily]

是access数据库啊

[poily]

[Quote=引用 2 楼 ljhcy99 的回复:]
SELECT *,0 AS CNT FROM TABLE
WHERE SORT <> NULL
UNION
SELECT *,1 AS CNT FROM TABLE
WHERE SORT IS NULL
ORDER BY CNT,SORT
[/Quote]

结果还是空值的记录在最前面
id sort
3 null
4 null
1 1
2 2
4 3
需要的结果是
id sort
1 1
2 2
4 3
3 null
4 null