Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    67

    Unanswered: Strange behaviour in DB2 9.7.6 WSE for LUW

    Since we moved from DB2 9.1.8 WSE for LUW to 9.7.6 WSE for LUW I experience sometimes a strange behaviour.

    The sentence
    db2 "select year(date_column),month(date_column),count(*) from table_name where column1_name<>0 and c=208 group by year(date_column),month(date_column)"
    should give first the year, second the month and last the count.

    In this case it gives

    1 2 3
    ----------- ----------- -----------
    1998 1 283
    1999 1 280
    2000 1 287
    2001 1 381
    2002 1 393
    2003 1 413
    2004 1 440
    2005 1 466
    2006 1 495
    2007 1 517
    2008 1 541
    2009 1 553
    2010 1 571
    2011 1 589
    2012 1 615
    2013 1 634
    2014 1 661
    1998 2 283
    1999 2 281
    .....
    ordered by month and then by year.

    In other cases it works as expected.

    This isn't an urgent problem. I just want to know if there is another solution than using "order by" after "group by".

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I don't see any
    Strange behaviour
    It looks as though you are getting exactly what you asked for, the year, the month and the count.
    I think you are trying to say that you do not like the ordering of the data that is returned. Order is never garaunteed without an order by. You may get data in the order you are expecting in some cases due to how the data is clustered, but even then some data might be out of order if you do not expressly define an ORDER BY statement.
    You, also, mention GROUP BY, again this has nothing to do with the ordering of the data that is returned to you.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to say similar opinions as dav1mo's(may be in some other words).

    I just want to know if there is another solution than using "order by" after "group by".
    No!
    You didn't specified ORDER BY clause,
    so the sequence of the results are not guarunteed.

    In other word,
    DB2 would try to choose most efficient way to return the expected results from the query you provided.
    DB2 would not expect more than that.

    If you didnt't specified ORDER BY clause,
    DB2 will make effort to produce the result with the most efficient way regardless the order of the result.


    DB2 don't care of your expectations(even if it might be natural for most humanbeings) beyond your provided queries.

  4. #4
    Join Date
    Nov 2004
    Posts
    67
    dav1mo and tonkuma,

    Thank you for your answers.


    This thread can be closed.

Posting Permissions

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