|
View previous topic :: View next topic
|
| Author |
Message |
Souvik.Sinha
New User
Joined: 22 Apr 2005
Posts: 24
Location: New York, USA
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
Souvik.Sinha
New User
Joined: 22 Apr 2005
Posts: 24
Location: New York, USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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  |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Souvik.Sinha
New User
Joined: 22 Apr 2005
Posts: 24
Location: New York, USA
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Souvik.Sinha
New User
Joined: 22 Apr 2005
Posts: 24
Location: New York, USA
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Good luck - we'll keep an eye on your question in the DFSORT part of the forum also 
d |
|
| Back to top |
|
|
|
|
