View previous topic :: View next topic
|
Author |
Message |
r arunmoezhi
New User
Joined: 26 Jun 2009 Posts: 32 Location: Chennai
|
|
|
|
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 |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
r arunmoezhi
New User
Joined: 26 Jun 2009 Posts: 32 Location: Chennai
|
|
|
|
@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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
|