help with select statement?

SQL Navigator
1

Hello,

I need to come up with a sql select statement that will find all rows in which the operation, interface,ocrelease and products is unique. However, for all these rows, I also need to display the other columns. It could be the first instance found.

Any idea? I started to look at group by and rowcount, but not sure how to apply over multiple columns. Thanks

The table has 2 clob fields.

example:

table

operation interface ocrelease rundate products server request request2 response
O1 I1 OC1 R1 P1 S1 R1 R21 RE1
O1 I1 OC1 R1 P1 S2 R11 R31 RE2
O1 I2 OC1 R1 P3 S3 R3 R31 RE2

So, in the above, it would return all the columns for the 1st row and the 3rd row.

Schema:

OPERATION 1 1 N VARCHAR2 (50 Byte) Frequency 89
INTERFACE 2 Y VARCHAR2 (50 Byte) None 20
OCRELEASE 3 2 N VARCHAR2 (8 Byte) Frequency 10
RUNDATE 4 Y DATE None 545
PRODUCTS 5 3 N VARCHAR2 (255 Byte) Frequency 56
SERVER 6 Y VARCHAR2 (50 Byte) None 117
REQUEST 7 4 N VARCHAR2 (4000 Byte) Frequency 41
REQUEST2 8 Y CLOB None 0
RESPONSE 9 Y CLOB None 0

2

Hi apatnaik_187,

I would imagine something like:

select a.* from tab a, (select distinct o, i, oc, p from tab) b
where a.o=b.o and a.i=b.i and a.oc=b.oc and a.p=b.p

I’m sure SQL experts can suggest a better solution.

Regards,
Roman

3

That doesn’t seem to work. I get ambiguous error message on products. Also, tab a in your example would have multiple rows in which it matches the subtable b. Anyone else have any suggestions? Thanks

4

Ok, so with Roman’s select above, change the query to MIN() for all returned columns

select a.*
from tab a,
(select subtab.o, subtab.i, subtab.oc, subtab.p, min(subtab.R) r_1,
min(subtab.S) s_1, min(subtab.R1) r1_1, min(subtab.R2) r2_1,
min(subtab.RE) re_1
from tab subtab
group by subtab.o, subtab.i, subtab.oc, subtab.p) b
where a.o=b.o
and a.i=b.i
and a.oc=b.oc
and a.p=b.p
and a.r = b.r_1
and a.s=b.s_1
and a.r1 = b.r1_1
and a.r2 = b.r2_1
and a.re = b.re_1

I do not have this table so my attempt has not been validated for syntax. Sorry about that.

BTW: I’m not that smart so this may not work.