CODEWITHSUNDEEP
sqldb2
reto
reto

Reputation: 15

SQL order two different columns in DB2

I have following SQL:

EXEC SQL
  DECLARE MY-CUR CURSOR WITH HOLD FOR
  SELECT ,ME.MSGTYPECD
         ,ME.MSGSUBTYPECD
  FROM    T_MESSAGE ME
ORDER BY  ME.MSGTYPECD ASC,
          ME.MSGSUBTYPECD ASC

The result set is lookin like this:

MsgType     MsgSubType
300 
300 
515 
515 
515 
535 
535 
535 
598         515
598         515
598         515

What I would like to acomplish is following result:

MsgType     MsgSubType
300 
300 
515 
515 
515 
598         515
598         515
598         515
535 
535 
535

There is also a possible solution to do this task with ORDER BY and CASE, but then I would need to "code" every MsgType which is a bit annoying. (900 MsgTypes) and if a new one comes in I would need to change the query again.

edit:// MsgType and MsgSubType is defined as character and the value (if not is filled) ist BLANK.

The definition looks like this:

with T_MESSAGE (MsgTypeCD, MsgSubTypeCD) as (values
  ('300', ' ')
, ('300', ' ')
, ('515', ' ')
, ('515', ' ')
, ('515', ' ')
, ('535', ' ')
, ('535', ' ')
, ('535', ' ')
, ('598', '515')
, ('598', '515')
, ('598', '515')
)

Upvotes: 0

Views: 60

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

You haven't provided a formal description on exact rules of getting this result, but I'll try to guess.

The query below returns the desired output.

with T_MESSAGE (MsgTypeCD, MsgSubTypeCD) as (values
  ('300', ' ')
, ('300', ' ')
, ('515', ' ')
, ('515', ' ')
, ('515', ' ')
, ('535', ' ')
, ('535', ' ')
, ('535', ' ')
, ('598', '515')
, ('598', '515')
, ('598', '515')
)
SELECT ME.MSGTYPECD, ME.MSGSUBTYPECD
FROM T_MESSAGE ME
ORDER BY 
  COALESCE(nullif(ME.MSGSUBTYPECD, ''), ME.MSGTYPECD)
, ME.MSGSUBTYPECD

Upvotes: 3

Related Questions