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
 

 

Query needed for fetching rows in same table.

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

New User


Joined: 29 May 2006
Posts: 0

PostPosted: Wed Aug 23, 2006 10:50 am    Post subject: Query needed for fetching rows in same table.
Reply with quote

A table has column names as a,b,c,d,e,f

I need a query such that to select two or more rows rows in the same

table having equal values of a,b and c should be different .

for the values d,e,f any one of the values should be different.

for example
a b c d e f

3 5 7 9 11 16
3 5 8 9 11 1

or
a b c d e f

3 5 7 9 11 16
3 5 8 9 6 16

or
a b c d e f

3 5 7 9 11 16
3 5 8 2 6 21

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

nuck

New User


Joined: 09 Dec 2005
Posts: 33

PostPosted: Wed Aug 23, 2006 2:25 pm    Post subject:
Reply with quote

if I understand what you're saying(you want the rows where a and b match, but c is different), then join your table to itself....

select a.a,a.b,a.c,a.d,a.e,a.f
from your_table a
, your_table b
where a.a = b.a
and a.b. = b.b
and a.c <> b.c
Back to top
View user's profile Send private message
cristalclearwaters

New User


Joined: 21 Aug 2006
Posts: 13

PostPosted: Wed Aug 23, 2006 5:16 pm    Post subject: Re: Query needed for fetching rows in same table.
Reply with quote

Adding more to that query to cover columns d, e, and f:

where (a.a=b.a
and a.b=b.b
and a.c <>b.c)
AND (( a.d = b.d
and a.e = b.e
and a.f <> b.f)
OR (a.d = b.d
and a.e <> b.e
and a.f = b.f)
OR (a.d <> b.d
and a.e = b.e
and a.f = b.f))
Back to top
View user's profile Send private message
nuck

New User


Joined: 09 Dec 2005
Posts: 33

PostPosted: Wed Aug 23, 2006 5:22 pm    Post subject:
Reply with quote

I'd be careful with 'OR's, they can really eat up performance - an alternative is to use UNIONs.

another solution: if you only care about col a, b, c, you could use count(*) with group and having....eg.

select a,b, count(*) from your_table group by a,b, having count(*) > 1

...just depends on your requirements...
Back to top
View user's profile Send private message
ksathishkumar4u
Currently Banned

New User


Joined: 29 May 2006
Posts: 0

PostPosted: Wed Aug 23, 2006 5:51 pm    Post subject:
Reply with quote

It worked fine

Thanks a lot
Back to top
View user's profile Send private message
ksathishkumar4u
Currently Banned

New User


Joined: 29 May 2006
Posts: 0

PostPosted: Thu Aug 24, 2006 11:21 am    Post subject: Re: Query needed for fetching rows in same table.
Reply with quote

IT WORKED FINE

But when i use all the OR condition the db2 displays Resource Over limit.

I removed the OR's ,query worked fine.

Thanks
Back to top
View user's profile Send private message
cristalclearwaters

New User


Joined: 21 Aug 2006
Posts: 13

PostPosted: Fri Aug 25, 2006 5:12 pm    Post subject: Re: Query needed for fetching rows in same table.
Reply with quote

So I am curious what you did with rows d, e, and f. Did you use the UNION as Nuck suggested?
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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
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


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