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
 

 

Remove duplicates from Query Results

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Remove duplicates from Query Results
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    Post subject: Reply to: Remove duplicates from Query Results
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    Post subject: Reply to: Remove duplicates from Query Results
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

Senior Member


Joined: 21 Sep 2010
Posts: 1625
Location: NY,USA

PostPosted: Tue Feb 28, 2012 5:32 pm    Post subject:
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    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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts Vision results (also known as DYL-280) CaptBill CA Products 1 Tue Dec 20, 2016 3:35 am
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


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