Query needed for fetching rows in same table.

ksathishkumar4u · New User Joined: 29 May 2006 Posts: 0

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

nuck · New User Joined: 09 Dec 2005 Posts: 33

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

cristalclearwaters · New User Joined: 21 Aug 2006 Posts: 13

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))

nuck · New User Joined: 09 Dec 2005 Posts: 33

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...

ksathishkumar4u · New User Joined: 29 May 2006 Posts: 0

It worked fine

Thanks a lot

ksathishkumar4u · New User Joined: 29 May 2006 Posts: 0

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

cristalclearwaters · New User Joined: 21 Aug 2006 Posts: 13

So I am curious what you did with rows d, e, and f. Did you use the UNION as Nuck suggested?