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
 

 

Ordering result based on aggregate condition

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Ordering result based on aggregate condition
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Mon May 03, 2010 2:39 pm    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Mon May 03, 2010 7:37 pm    Post subject:
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

Site Director


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

PostPosted: Mon May 03, 2010 7:42 pm    Post subject:
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    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
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


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