Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Ranking of Values Present in Different Columns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Ranking of Values Present in Different Columns
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

Site Director


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

PostPosted: Tue Jun 11, 2013 7:02 pm    Post subject:
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: 2158
Location: @my desk

PostPosted: Tue Jun 11, 2013 7:08 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Tue Jun 11, 2013 7:25 pm    Post subject:
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: 2158
Location: @my desk

PostPosted: Tue Jun 11, 2013 7:51 pm    Post subject:
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

Site Director


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

PostPosted: Tue Jun 11, 2013 8:30 pm    Post subject:
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Tue Jun 11, 2013 9:00 pm    Post subject: Reply to: Ranking of Values Present in Different Columns
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

Site Director


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

PostPosted: Tue Jun 11, 2013 11:29 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Wed Jun 12, 2013 12:51 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us