Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2014
    Posts
    3

    Unanswered: Querying help for DB2

    Here is the sample data set. I would like to query 2 records per each

    Table A
    COL A COL B

    1 11

    1 12

    1 13

    2 10

    3 21

    2 22

    2 23

    3 32

    3 33

    My result should display any records per each distinct record in COL A:

    1 11
    1 13
    2 22
    2 23
    3 32
    3 33

    Any help or guidance is greatly appreciated.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What have you tried?

  3. #3
    Join Date
    Dec 2014
    Posts
    3

    Query help

    I tried grouping by COL A and tried using having count =1 but didnt work. I dont want to use union as my working dataset is much larger and complicated..it beats the purpose.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I would suggest using a rank and then selecting where rank < 2. Good luck and let us see your result.
    Dave

  5. #5
    Join Date
    Dec 2014
    Posts
    3

    Query Help

    Dave,

    Thanks a lot.

    I used..

    select A.* from (
    select COL A, COL B,rank() over (partition by COL A order by COL B desc) as rank
    from TABLE ) A
    where A.rank <3

Posting Permissions

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