Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003

    Question Unanswered: DB2 sorting the numbers by default

    Hi, I am running a simple select query to get some numbers back like the following.

    select * from TABLE where NUMBER in ('0334','0980','0116','0633','0116D','0115','0114' ,'0141','0635','8371','0240','0142','0316','2478', '0913','0108','0982','0731','0147','2460').

    I am expecting the results in the order the IDs are mentioned in the query. But DB2 is by default sorting the number in the resultset. So the first record is showing as 0108 instead of 0334. Is there any way I can turn-off this default ordering of numbers.

    Thanks in advance for your help.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    The only way that I know to change the order of a result set is to use the ORDER BY clause. You'll need to get a bit tricky to get the apparently arbitrary order you want, but you can do it using a CASE statement in the ORDER BY clause.


  3. #3
    Join Date
    Apr 2003


    I think CASE can be used to substitute something in the results based on some condition. But in my case, I just need to display the numbers in the order it was given is the Query. I am still trying to find some solution.
    Thanks again!

  4. #4
    Join Date
    Aug 2004
    Try something like this...

    select .... ,
    case NUMBER
    WHEN '0334' THEN 1
    WHEN '0980' THEN 2 ....
    ...WHEN '2460' THEN 20
    end as NUM_ORDER
    from TABLE
    where NUMBER in ('0334','0980','0116','0633','0116D','0115','0114' ,'0141','0635','8371','0240','0142','0316','2478', '0913','0108','0982','0731','0147','2460')
    order by NUM_ORDER

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts