What's the Equivalent of Oracle's NULLS FIRST in DB2?

Does DB2 have anyth开发者_运维百科ing like ORDER BY ord_col NULLS FIRST?

You use a case in the order by to sort nulls before other values:

select  *
from    YourTable
order by
        case when col1 is null then 0 else 1 end
,       col1

There is nothing such like ORDER BY ord_col NULLS FIRST in DB2, but you can use functions to get the same behavior:

CREATE TABLE TV84 (
   CORN CHAR(2) NOT NULL
  ,COUAG CHAR(4) NOT NULL
  ,DDFA DATE NOT NULL
  ,MTFAC1 INTEGER
);

INSERT INTO TV84 (CORN,COUAG,DDFA,MTFAC1)
VALUES ('01','0309',TO_DATE('10.05.2008','MM.DD.YYYY'),2520);
INSERT INTO TV84 (CORN,COUAG,DDFA,MTFAC1)
VALUES ('01','0309',TO_DATE('12.14.2008','MM.DD.YYYY'),NULL);
INSERT INTO TV84 (CORN,COUAG,DDFA,MTFAC1)
VALUES ('01','0309',TO_DATE('10.15.2009','MM.DD.YYYY'),6452);
INSERT INTO TV84 (CORN,COUAG,DDFA,MTFAC1)
VALUES ('02','0309',TO_DATE('06.16.2010','MM.DD.YYYY'),1283);
INSERT INTO TV84 (CORN,COUAG,DDFA,MTFAC1)
VALUES ('02','0309',TO_DATE('08.28.2010','MM.DD.YYYY'),NULL);

• With the COALESCE scalar function:

SELECT CORN, COUAG, DDFA, MTFAC1
FROM TV84
ORDER BY COALESCE(MTFAC1,0);

CORN COUAG DDFA       MTFAC1
---- ----- ---------- -----------
01   0309  14.12.2008           -
02   0309  28.08.2010           -
02   0309  16.06.2010        1283
01   0309  05.10.2008        2520
01   0309  15.10.2009        6452

• Or more explicit with the ROW_NUMBER OLAP function (see OLAP functions):

SELECT CORN, COUAG, DDFA, MTFAC1
  ,ROW_NUMBER() OVER (ORDER BY MTFAC1 NULLS FIRST) RN
FROM TV84
ORDER BY RN;

CORN COUAG DDFA       MTFAC1      RN
---- ----- ---------- ----------- --------------------
01   0309  14.12.2008           -                    1
02   0309  28.08.2010           -                    2
02   0309  16.06.2010        1283                    3
01   0309  05.10.2008        2520                    4
01   0309  15.10.2009        6452                    5

Update: For the OLAP function solution, if you need to order by an additional column, it's better to use the RANK or DENSE_RANK functions:

SELECT CORN, COUAG, DDFA, MTFAC1
  ,RANK() OVER (ORDER BY MTFAC1 NULLS FIRST) RNK
FROM TV84
ORDER BY RNK;

CORN COUAG DDFA       MTFAC1      RNK
---- ----- ---------- ----------- --------------------
01   0309  14.12.2008           -                    1
02   0309  28.08.2010           -                    1
02   0309  16.06.2010        1283                    3
01   0309  05.10.2008        2520                    4
01   0309  15.10.2009        6452                    5

No, I don't believe so.

You can emulate the behaviour with something like:

select 0 as seq, col1, col2 from tbl where col1 is null
union all
select 1 as seq, col1, col2 from tbl where col1 is not null
order by 1, 2

and then ignoring the seq column in your data processing code.