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
 

 

Selecting duplicate columns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
hcl_ln

New User


Joined: 14 Sep 2005
Posts: 21

PostPosted: Tue Aug 21, 2007 9:22 pm    Post subject: Selecting duplicate columns
Reply with quote

Hello,

I have a table like this:

ColA ColB ColC
123456 1996-01-01 983.50
123456 1996-01-01 1290.01
123456 2007-01-01 560.05
123456 2006-01-01 893.50

I need to select only the first two rows of the table as the output, meaning I need to get the rows with same ColA and ColB but with diff ColC, as below.

ColA ColB ColC
123456 1996-01-01 983.50
123456 1996-01-01 1290.01

I have written the query like this:

SELECT ColA, ColB, ColC
FROM T1
where ColA = '123456'
GROUP BY ColA, ColB, ColC
HAVING COUNT(ColB) > 1 ;

But the result is Zero rows.
If I remove the ColC from the query, it is retrieving only one row instead of two. Can somebidy help?

Thanks in advance !
Back to top
View user's profile Send private message

Balraj

New User


Joined: 16 Aug 2007
Posts: 34
Location: Bangalore

PostPosted: Wed Aug 22, 2007 10:01 am    Post subject: Reply to: Selecting duplicate columns
Reply with quote

Hai

Try out this Query

SELECT ColA, ColB, ColC
FROM T1
where ColA = '123456' AND ColB='1996-01-01 '
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Aug 22, 2007 2:12 pm    Post subject:
Reply with quote

Code:

SELECT A.COLA
    ,  A.COLB
    ,  A.COLC
FROM T1 A
  ,  T1 B
WHERE A.COLA = B.COLA
  AND A.COLB = B.COLB
  AND A.COLC <> B.COLB;


or

Code:

SELECT A.COLA
    ,  A.COLB
    ,  A.COLC
FROM T1 A JOIN  T1 B
   ON A.COLA = B.COLA
  AND A.COLB = B.COLB
WHERE A.COLC <> B.COLB;
Back to top
View user's profile Send private message
hcl_ln

New User


Joined: 14 Sep 2005
Posts: 21

PostPosted: Wed Aug 22, 2007 7:53 pm    Post subject: Reply to: Selecting duplicate columns
Reply with quote

Thank you dbzTHEdinosauer & Balraj,

Balraj, Since the values I have given are examples only and I donot know how many of these type of rows are there, I can use host variables. But thanks for the suggestion.

dbzTHEdinosauer,
Thank you for providing the solutions. I have tried both the queries and gave my desired results.

The third condition in the WHERE clause should be: A.COLC <> B.COLC.
I think this is a typo, but I do appreciate your efforts.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Aug 22, 2007 8:00 pm    Post subject:
Reply with quote

hcl_ln,

good catch. I did not do that intentionally, a typo as you said.
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts how to find the duplicate list RAVIGUPTA1990 DB2 6 Wed Mar 30, 2016 12:11 am
No new posts Combine columns without breaking words Indrajit_57 SYNCSORT 1 Sat Mar 19, 2016 9:33 am


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