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 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 Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm

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