Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007
    Posts
    34

    Post Unanswered: How to get the highest values per given col

    have 2 tables X and Y in DB2 V8 db

    Table X
    -----------------------------------
    COL S COL V COL S
    --------- ------------ ---------
    S1 A TT
    S1 B TT
    S1 C TT
    S2 F TT
    S3 D TT
    S3 Z TT

    Table Y
    -----------------------------------
    COL V COL N
    ----------- --------------
    A 30
    B 20
    C 60
    F 10
    D 80
    Z 40

    Result needs to be 1 row per distinct COL S values and the one with the highest value in COL N . So in above case :
    S1 C 60
    S2 F 10
    S3 D 80


    Thanks

  2. #2
    Join Date
    Jul 2007
    Posts
    34
    Found it .
    select X.S,MAX(Y.N) from X,Y
    where X.V = Y.V
    group by X.S;

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    with t1 (s,v,n) as
    (select x.cols,x.colv,t.coln from x inner join y on (x.colv = y.colv)),
    t2 (s,n) as
    (select s,max(n) from t1 group by s)
    select * from t1 where (s,n) in (select * from t2)

    Andy

Posting Permissions

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