# Ranking of Values Present in Different Columns

Author Message
Souvik.Sinha

New User

Joined: 22 Apr 2005
Posts: 24
Location: New York, USA

Posted: Tue Jun 11, 2013 2:47 am

I have a table with the following structure:
 Code: Category   Reason1   Reason2   Reason3 --------   -------   -------   ------- 001        AA1       DR1       BW9 001        CC3       AA1       EW9 001        BW7       DR1       AA1 002        AA1       CC3       BW9 003        EW9       TY1       CC3 003        BW9       DR7       FE8 777        AA1       CC3       BW9 777        AA1       CC3       DR1 777        AB1       EW9       BW9

My requirement is to provide the top 3 Reason Codes along with their count for each Category. The final output should look like:
 Code: Category   Reason1   Reason2   Reason3 --------   -------   -------   ------- 001        AA1(3)    DR1(2)    BW7(1) 002        AA1(1)    CC3(1)    BW9(1) 003        BW9(1)    BW9(1)    CC3(1) 777        AA1(2)    BW9(2)    CC3(2)

Please note that where there are ties, the tied Reason Codes should be in alphabetical order. However, if that's too hard to be achieved, any order is fine (for the tied Reason Codes). Can this be done using SQL queries?
dick scherrer

Moderator Emeritus

Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

 Posted: Tue Jun 11, 2013 7:02 pm Hello, I do not understand the "rules". . . How is a "top reason code" defined? What is considered a "tie"? If you will clarify, it may help someone help you.
Arun Raj

Moderator

Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

 Posted: Tue Jun 11, 2013 7:08 pm Hi Dick, I guess the OP is trying to extract the max-occurring reason codes across all the reason types for each category. For e.g., For category - 001, AA1 occurs 3 times which should be ranked first, DR1 2 times, and BW7 once which came third due to the alphabetic prcedence rule since there are 'ties'. But I guess for category- 003, he made a typo for Reason2 and Reason3 outputs - Should nt they be CC3(1) and DR7(1) respectively
Souvik.Sinha

New User

Joined: 22 Apr 2005
Posts: 24
Location: New York, USA

Posted: Tue Jun 11, 2013 7:25 pm

Hi Arun,
You are correct. I am trying to rank them by maximum-occurring Reason Codes.

And for Category 003, I made typo. The corrected output should be:
 Code: Category   Reason1   Reason2   Reason3 --------   -------   -------   ------- 001        AA1(3)    DR1(2)    BW7(1) 002        AA1(1)    CC3(1)    BW9(1) 003        BW9(1)    CC3(1)    DR7(1) 777        AA1(2)    BW9(2)    CC3(2)
dick scherrer

Moderator Emeritus

Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

 Posted: Tue Jun 11, 2013 7:25 pm Hi Arun, I believe there is more wrong than category 003. . . The output for 002 is not in "order", is it? As i mentioned, i'm confused
Arun Raj

Moderator

Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

Posted: Tue Jun 11, 2013 7:51 pm

Hi Dick,

 Quote: 001 AA1 DR1 BW9 001 CC3 AA1 EW9 001 BW7 DR1 AA1
AA1 - thrice
DR1 - twice
rest all once, but ascending order BW7 comes first.
dick scherrer

Moderator Emeritus

Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

Posted: Tue Jun 11, 2013 8:30 pm

Hi Arun,

Yup, that part i see.

I son't see how this:
 Code: 002        AA1(1)    CC3(1)    BW9(1)
happens.
Souvik.Sinha

New User

Joined: 22 Apr 2005
Posts: 24
Location: New York, USA

Posted: Tue Jun 11, 2013 8:59 pm

Hi Dick,
I can't believe I made so many mistakes in such a small list! It should have been:
 Code: 002        AA1(1)    BW9(1)    CC3(1)
enrico-sorichetti

Superior Member

Joined: 14 Mar 2007
Posts: 10863
Location: italy

 Posted: Tue Jun 11, 2013 9:00 pm as I understand it the is on total number of occurrences for each reason/key alphabetical pick the first three for each key not difficult with sort bit more difficult with a single db2 query
dick scherrer

Moderator Emeritus

Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

Posted: Tue Jun 11, 2013 11:29 pm

Hello,

 Quote: I can't believe I made so many mistakes in such a small list!
As they say "Speed kills". . .

Agree with Enrico, i'd consider using your sort product.
Souvik.Sinha

New User

Joined: 22 Apr 2005
Posts: 24
Location: New York, USA

 Posted: Wed Jun 12, 2013 12:46 am Thanks Enrico and Dick for the feedback. I'll try to do it with Sort (and will post my question in DFSORT/ICETOOL forum).
dick scherrer

Moderator Emeritus

Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

 Posted: Wed Jun 12, 2013 12:51 am Good luck - we'll keep an eye on your question in the DFSORT part of the forum also d
 View Bookmarks All times are GMT + 6 Hours

 Topic Forum Replies Similar Topics Null values are considered in Total c... DFSORT/ICETOOL 6 DB2 Views with Multiple SQL & Col... DB2 8 Converting ASCII values to COMP-3 (ZD... JCL & VSAM 2 Generate output lines (SYSIN card for... DFSORT/ICETOOL 4 Conditional replace values in output ... DFSORT/ICETOOL 3
Search our Forums:

 IBMMainframes.com is not an official and/or affiliated with IBM® in anyway Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us