Results 1 to 6 of 6

Thread: Grouping in DB2

  1. #1
    Join Date
    Jan 2013
    Posts
    16

    Wink Unanswered: Grouping in DB2

    I have a problem. This is how my fields are displayed.

    Customer amt expense expkey seqno
    ATT 20 TEL 1 1
    BBB 66 SER 2 1
    BBB 66 SER 2 2
    CCC 40 DOG 1 1
    CCC 40 SER 1 1

    What I am looking for is an SQL that will return the all the records (should'nt filter any) but for each group of customer with the same expkey it will return the max value for the seqno. Therefore the results would look like this

    customer amt expense expkey max(seqno)
    ATT 20 TEL 1 1
    BBB 66 SER 2 2 (note how this displays the max )
    BBB 66 SER 2 2
    CCC 40 DOG 1 1
    CCC 40 SER 1 1

    The query that I tried it filters out the row - see below

    select max(seqno), customer,amt,expense,expkey
    from cust
    group by customer, amt,expense,expkey

    Can someone please assist me with putting together a proper query to reflect what I am looking for.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check out OLAP functions, particularly MAX(seqno) OVER (PARTITION BY customer, amt, expense)
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2013
    Posts
    16
    What I mean by it doesnt work is that it remove one of the line. Can you show me how I would write the query. I am not too familiar with the advance function OLAP function, partition and etc.

  4. #4
    Join Date
    Jan 2013
    Posts
    16
    The results would remove the second line. (notice the line with the BBB with exkey 1 is not in the result. I assume since it is not the max of the two it didnt display it. I would like the query to display all the rows but just give the max (seqno) of the two.

    cust amt expense expkey seqno
    ATT 20 TEL 1 1
    BBB 66 SER 2 2
    CCC 40 DOG 1 1
    CCC 40 SER 1 1

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    peadove,

    Even if you were not too familiar with the advance function OLAP function,
    you may want to use the expression which n_i was offered...
    Quote Originally Posted by n_i View Post
    ... MAX(seqno) OVER (PARTITION BY customer, amt, expense)
    More detailed descriptions are here, it might be better to sart from Examples at the bottom the page.
    OLAP specifications - IBM DB2 9.7 for Linux, UNIX, and Windows

  6. #6
    Join Date
    Jan 2013
    Posts
    16
    Thank you so much N1 and Tonkuma. Thank you, thank you, thank you. It worked. I am the happiest person alive.

Posting Permissions

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