Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
help needed in a query

 
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: Tue Dec 08, 2009 6:39 pm    Post subject: help needed in a query
Reply with quote

there are two columns in a table
Code:

COL A     COL B
CF           C2 
CF           C4 
CF           D9 
DH           D8 
DH           9F 
DH           9Z 
Z1           C2 
Z1           C4 
Z1           D9 
Z1           GN 
Z1           3Z 


My requirement is as follows:
Any value (eg: D9) from COL B would be specified in the query.

For the specified value the corresponding values in COL A should be obtained.
(eg: D9 in COL B would fetch CF and Z1 from COL A)

Code:

SELECT COL_A FROM TABLE
WHERE COL_B = 'D9'


Now we have a list of values from COL A. For this list I need to fetch all the values from COL B.
Eg:
for CF and Z1 from COL A the result set would be
Code:

COL A     COL B
CF           C2 
CF           C4 
CF           D9 
Z1           C2 
Z1           C4 
Z1           D9 
Z1           GN 
Z1           3Z 


Code:

SELECT COL_A, COL_B FROM TABLE
WHERE COL_A IN
(
     SELECT COL_A FROM TABLE
   WHERE COL_B = 'D9'
)

I need the above result to be ORDERED based on number of rows for each value in COL A.
Eg:
Since Z1 has 5 records and CF has 3 records the result should be reordered as
Code:

COL A     COL B
Z1           C2 
Z1           C4 
Z1           D9 
Z1           GN 
Z1           3Z
CF           C2 
CF           C4 
CF           D9 
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Dec 08, 2009 8:35 pm    Post subject:
Reply with quote

Code:

SELECT A.C1,A.C2,FROM
(SELECT C1,C2 FROM
TABLE
WHERE C1 IN
(SELECT C1 FROM TABLE
WHERE C2 ='C4')) AS A
,
(SELECT C1,COUNT(*) AS COUNT FROM TABLE GROUP BY C1) AS B
WHERE A.C1 = B.C1
ORDER BY B.COUNT DESC ;
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Dec 09, 2009 3:58 pm    Post subject:
Reply with quote

Code:

SELECT b.ColA,b.ColB, count(*) FROM TAB1 a
join TAB1 b on b.ColA = a.ColA
join TAB1 c on c.ColA  = c.ColA

WHERE a.ColB ='D9'
group by b.ColA,b.ColB
order by 3 desc
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Dec 09, 2009 4:22 pm    Post subject:
Reply with quote

Guyc,
Believe it should be
Code:
join TAB1 c on c.ColA  = b.ColA
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Dec 09, 2009 9:19 pm    Post subject:
Reply with quote

Srihari Gonugunta wrote:
Guyc,
Believe it should be
Code:
join TAB1 c on c.ColA  = b.ColA

oops, yes of course
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 Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
This topic is locked: you cannot edit posts or make replies. SORT trick needed bshkris SYNCSORT 6 Tue May 02, 2017 4:35 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us