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

Remove duplicates from Query Results
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
sasanka
Warnings : 1
New User


Joined: 18 Jan 2008
Posts: 34
Location: India
PostPosted: Tue Feb 28, 2012 4:33 pm
 Reply with quote
Hi,

I executed below query and found results as below:

Code:
SELECT DISTINCT A1.MANAGER_CODE                             
                          ,A1.STATUS_CODE                                     
                          ,B1.STATUS_CODE       
                          ,B1.PRODUCT_CODE                                     
FROM  MANAGER_TAB  A1                                 
         ,PRODUCT_TAB  B1                                 
WHERE A1.MANAGER_ID = B1.MANAGER_ID
   AND A1.MANAGER_CODE IN ('28B00Q0P', '28B00Q06', '28B00Q1K')
ORDER BY A1.MANAGER_CODE                                     
WITH UR;         


Result of the above query is:

Code:
MANAGER_CODE   STATUS_CODE  STATUS_CODE  PRODUCT_CODE
---------+---------+---------+---------+-------------------------------------
28B00Q0P                  H                            A                 SPS       
28B00Q0P                  H                            H                 SPS       
28B00Q06                  H                            A                 SPS       
28B00Q06                  H                            H                 SPS       
28B00Q1K                  A                            A                 SPS       

But my expected result is:

Code:
MANAGER_CODE   STATUS_CODE  STATUS_CODE  PRODUCT_CODE
---------+---------+---------+---------+-------------------------------------
28B00Q0P                  H                            A                 SPS       
28B00Q06                  H                            A                 SPS       
28B00Q1K                  A                            A                 SPS       

I need one row for each distinct MANAGER_CODE. Suppose there are three rows for the same MANAGER_CODE, retriving any one row is fine.

Please any one help me on this query.
Back to top
View user's profile Send private message
sasanka
Warnings : 1
New User


Joined: 18 Jan 2008
Posts: 34
Location: India
PostPosted: Tue Feb 28, 2012 5:01 pm
 Reply with quote
I have also tried the below query and got one row more

Code:
SELECT
     ( SELECT A1.MANAGER_CODE
         FROM MANAGER_TAB A1
       WHERE A1.MANAGER_ID = B1.MANAGER_ID
       FETCH FIRST 1 ROW ONLY
     )
       ,A1.STATUS_CODE
       ,B1.STATUS_CODE
       ,B1.PRODUCT_CODE
FROM MANAGER_TAB A1
        ,PRODUCT_TAB B1
WHERE A1.MANAGER_ID = B1.MANAGER_ID
    AND A1.MANAGER_CODE IN ('28B00Q0P', '28B00Q06', '28B00Q1K')   
ORDER BY A1.MANAGER_CODE
WITH UR;

The output of above query is
Code:
MANAGER_CODE    STATUS_CODE    STATUS_CODE    PRODUCT_CODE
28B00Q0P                         H                       A                        SPS 
28B00Q0P                         H                       H                        SPS 
28B00Q06                         H                       A                        SPS 
28B00Q06                         H                       H                        SPS 
28B00Q1K                         A                       A                        SPS 
28B00Q1K                         A                       A                        SPS
Back to top
View user's profile Send private message
Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM
PostPosted: Tue Feb 28, 2012 5:05 pm
 Reply with quote
try adding this to your where clause in your first post query

A1.STATUS_CODE =B1.STATUS_CODE
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Tue Feb 28, 2012 5:32 pm
 Reply with quote
try doing group by MANAGER_CODE STATUS_CODE STATUS_CODE PRODUCT_CODE in your query
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 Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Remove leading zeroes SYNCSORT 4
No new posts Query on edit primary command CLIST & REXX 5
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us