Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2014
    Posts
    1

    Unanswered: Need help to fix the sql

    Hello All,

    I am trying to find second highest record for each group. I have return the quesry as below. It is taking longer time to execute. So if anyone can help me to madify it, that would be great.

    The query is as follows...

    Select col A,col B ,col C,col D,col E, col F, col G, col H , col I from ABC.TABLE1
    Where ( col A, col B ) IN
    (Select col A,Max( col B)
    From ABC.TABLE1 Where (col A, col B) Not In (Select col A ,Max(col B) From ABC.TABLE1 Group by A)
    Group by A)



    Sample data:

    In the below data, there are 12 records, three groups, gor each group Col A value will be same, Col B value will be unique for each group, they will always be between 1 and 12.

    Col A, Col B, Col C, Col D, Col E, Col F
    101 , 5 , A , 20 , 30 , X
    101 , 4 , S , 10 , 26 , Z
    101 , 1 , C , 15 , 24 , R
    101 , 3 , B , 20 , 44 , S

    102 , 7 , W , 20 , 20 , D
    102 , 2 , F , 40 , 53 , T
    102 , 1 , Z , 75 , 34 , A
    102 , 3 , V , 10 , 34 , S

    103 , 4 , V , 20 , 31 , X
    103 , 11 , W , 90 , 22 , D
    103 , 2 , C , 35 , 32 , D
    103 , 9 , S , 30 , 45 , S

    Output

    101 , 4 , S , 10 , 26 , Z
    102 , 3 , V , 10 , 34 , S
    103 , 9 , S , 30 , 45 , S

    Let me know if any other clarification is needed. Thank you.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Let me know if any other clarification is needed.
    What DB2 version/release/fixpack and platform OS are you using?

    An alternative may be using ROW_NUMBER() OVER(PARTITION BY col_a ORDER BY col_b DESC).
    Please see
    OLAP specifications - IBM DB2 9.7 for Linux, UNIX, and Windows

Posting Permissions

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