IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Ranking of Values Present in Different Columns


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Souvik.Sinha

New User


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

PostPosted: Tue Jun 11, 2013 2:47 am
Reply with quote

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?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Tue Jun 11, 2013 7:02 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Tue Jun 11, 2013 7:08 pm
Reply with quote

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
Back to top
View user's profile Send private message
Souvik.Sinha

New User


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

PostPosted: Tue Jun 11, 2013 7:25 pm
Reply with quote

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)
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Tue Jun 11, 2013 7:25 pm
Reply with quote

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 icon_confused.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Tue Jun 11, 2013 7:51 pm
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Tue Jun 11, 2013 8:30 pm
Reply with quote

Hi Arun,

Yup, that part i see.

I son't see how this:
Code:
002        AA1(1)    CC3(1)    BW9(1)
happens.
Back to top
View user's profile Send private message
Souvik.Sinha

New User


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

PostPosted: Tue Jun 11, 2013 8:59 pm
Reply with quote

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)
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10833
Location: italy

PostPosted: Tue Jun 11, 2013 9:00 pm
Reply with quote

as I understand it the <sort> 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
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Tue Jun 11, 2013 11:29 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Souvik.Sinha

New User


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

PostPosted: Wed Jun 12, 2013 12:46 am
Reply with quote

Thanks Enrico and Dick for the feedback. I'll try to do it with Sort (and will post my question in DFSORT/ICETOOL forum).
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Jun 12, 2013 12:51 am
Reply with quote

Good luck - we'll keep an eye on your question in the DFSORT part of the forum also icon_wink.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Generate output lines (SYSIN card for... DFSORT/ICETOOL 4
No new posts Conditional replace values in output ... DFSORT/ICETOOL 3
No new posts Join files where value in one is betw... DFSORT/ICETOOL 6
No new posts SORT - To repeat a string in same col... SYNCSORT 3
No new posts JCL - To repeat a string in same colu... JCL & VSAM 2
Search our Forums:

Back to Top