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

Ordering result based on aggregate condition


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Mon May 03, 2010 2:14 pm
Reply with quote

table1:
Code:

PKG_CD     ACCY_CD   
CHAR(4)      CHAR(4)   
************************
CF           C1         
CF           C4         
CR           C1         
CR           D9         
SP           A4         
SP           I1         
SP           Q6         
SP           3Y         
SP           47         
SP           48         
Z1           C1         
Z1           C4         
Z1           GN         
Z1           3Z         

table2:
Code:

PKG_CD  ACCY_CD
CHAR(4)  CHAR(4)     
*********************
CR       D9         
CR       C1         
CT       CT         
E5       E5         
GN       GN         
MF       MF         
R3       R3         
WL       WL         
Z1       3Z         
Z1       GN         
Z1       C4         
Z1       C1         
Z2       CT         
Z2       GN         
Z2       3Z         
3Z       3Z         

Desired Result Set:
Code:

PKG_CD   COUNT             
----  -----------
SP              6
Z1              4
CR              2
CF              2

I wrote the below query to get the desired result:
Code:

SELECT PKG_CD,COUNT(PKG_CD) AS COUNT
FROM TABLE1
GROUP BY PKG_CD
ORDER BY COUNT(PKG_CD) DESC

My new requirement is whenever two packages have same number of accessory count (Eg. CR and CF having 2 accessories), the order of the results have to determined based on their presense in table2.
In this scenario, CR is present in table2. So in the result set CR should appear before CF.
Is this possible?
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon May 03, 2010 2:21 pm
Reply with quote

Quote:
the order of the results have to determined based on their presense in table2.
I think you are not familiar enough with DB2(RDBMS).

Also I couldn't figureout the rules for your desired result. How it relates table1 and table2?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon May 03, 2010 2:39 pm
Reply with quote

this works, but maybe isn't the best way.

Code:
SELECT PKG_CD,COUNT(PKG_CD) AS COUNT
,exst
FROM TABLE1
, table (select 1 as exst from sysibm.sysdummy1
     where exists(select * from TABLE2 where table1.PKG_CD = table2.PKG_CD)) B on 1=1
GROUP BY PKG_CD
ORDER BY COUNT(PKG_CD) DESC,exst
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Mon May 03, 2010 3:17 pm
Reply with quote

@gUYc:
Made some minor modifications and it worked like gem. Instead of INNER JOIN i used LEFT OUTER JOIN. QMF showed a cost of 44. But doesn't matter. There are only few hundred records in both the tables. Thanks a ton!!

Guess you have used correlated sub query. Can you please explain your approach.

@Escapa:
When someone was able to solve my problem then its obvious that my question was clear. May be you are not familiar with DB2 and RDBMS concepts.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon May 03, 2010 7:37 pm
Reply with quote

It is kind of a join with a correlated subquery.
Thats why you need the keyword "table".

The second "table " in the join only gets 1 row if it exists, 0 rows when it doesn't (that's why you need a left join instead of my inner join)
so exst can have value 1 or null

Another way was to have two uncorrelated queries and join those.
Code:
select a.pkg_cd,a.count, b.exst
from
  (select PKG_CD,COUNT(PKG_CD) as count from table1 group by PKG_CD) A
left join
  (select PKG_CD,1             as exst  from table2 group by PKG_CD) B
on a.pkg_cd = b.pkg_cd
order by b.count, a.exst

but then it would need to materialize query2 completely.
A correlated query is sometimes faster.
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: Mon May 03, 2010 7:42 pm
Reply with quote

Hello,

Quote:
When someone was able to solve my problem then its obvious that my question was clear.
Obvious - no. Not even close. . . The question was not very clear. Consider: you posted 2 tables but only refer to one in your initial "solution". There are values in table2 that did not appear in your "result". . . Etc. . .

You got lucky that Guy was one of the first to see your topic was able to decipher what you really wanted. An incorrect guess could have as easily been made by someone/anyone else.
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 first column truncated in search result IBM Tools 13
No new posts To search DB2 table based on Conditio... DB2 1
This topic is locked: you cannot edit posts or make replies. Merge 2 input files based on the reco... JCL & VSAM 2
No new posts SDSF like solution in EJES (store com... All Other Mainframe Topics 4
No new posts executing XCTL command in COBOL witho... CICS 10
Search our Forums:

Back to Top