Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2014
    Posts
    23

    Unanswered: sql order by 0 last

    I'm trying to put all of the 0 values last, but the column is in ascending order.

    I'm told the below examples work for DB2, but I don't know why they don't work for me.

    select distinct * from tbl order by col1 == 0, col1

    IBM.Data.DB2.iSeries.iDB2SQLErrorException: SQL0104 Token = was not valid. Valid tokens: FOR SKIP WITH FETCH OPTIMIZE.

    select distinct * from tbl order by col1 nulls last

    IBM.Data.DB2.iSeries.iDB2SQLErrorException: SQL0199 Keyword NULLS not expected. Valid tokens: FOR SKIP WITH FETCH OPTIMIZE.

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

    IBM.Data.DB2.iSeries.iDB2SQLErrorException: SQL0199 Keyword CASE not expected. Valid tokens: FOR SKIP WITH FETCH ORDER UNION EXCEPT OPTIMIZE.
    Last edited by EiBot; 11-26-14 at 11:06.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    These don't look like DB2 error messages.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2014
    Posts
    23
    I've edited my post, but I'm not sure what to say.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Your last variant should work, unless you're on some really ancient version of i Series. Also I'm a bit confused by your statement that you are "trying to put all of the 0 values last" but are in fact comparing values with NULL, which is a different thing, and then sorting them first. Assuming that you indeed mean zeroes, not NULLs, and therefore also assuming that COL1 has a numeric data type, you could try a different variant of the CASE expression:

    Code:
    select distinct * from tbl order by case col1 when 0 then 0 else -1 end, col1
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2014
    Posts
    23
    Hm.. Well, I've tried that and unfortunately got this error..

    IBM.Data.DB2.iSeries.iDB2SQLErrorException: SQL0214 ORDER BY expression is not valid.

    I've decided to try adding "Union" to same table where the col1 is 0 and it works... But it becomes a mess, since I have to make it work with 3 "where" conditions and 3 more "order by" conditions. The "order by" makes it fail. The last thing I tried is something I have to type out in order to describe it.

    select col1, col2, col3
    from (
    select 1 as rank, col1, col2, col3 from tbl where col1 <> 0
    union
    select 2 as rank, col1, col2, col3 from tbl where col1 = 0
    ) as tbl2
    order by
    rank,
    col2 desc,
    col3 asc

    fetch first 50 rows only

    and there's a error reporting on a comma... somewhere in there.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    there are some differences on Iseries, which is where you are running. What about changing your case up a bit? Instead of in the ORDER BY clause, put it in your SELECT statement. Something like:

    Code:
    select case col1 when 0 then 0 else -1 end as A, distinct * 
        from tbl order by A, col1
    Dave

  7. #7
    Join Date
    Jan 2014
    Posts
    23
    I am not aware of these differences, but that worked dav1mo. I never would've guessed to put it there. Thanks!!

Posting Permissions

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