Toad World® Forums

help with select statement?

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

SELECT DISTINCT OPERATION,

INTERFACE,

OCRELEASE,

RUNDATE,

PRODUCTS,

SERVER,

REQUEST,

REQUEST2,

RESPONSE

FROM TABLENAME;

How about this?

I assume you mean return only one row for all distinct values of operation+interface+ocrelease+products, and the other columns for any one of those rows having that particular combination of operation+interface+ocrelease+products.

I am assuming this is an Oracle database.

Solution 1: use analytic function row_number ()
select * from
(select row_number () over (partition by operation, interface, ocrelease, products order by operation, interface, ocrelease, products) as rn,
mytab.*
from mytab) x
were x.rn = 1 ;

Solution 2:
Use rowid pseudo-column because we know that rowid is unique. Instead of rowid you could use any combination of unique columns, e.g. PK columns

select * from mytab
where rowid in (select min (rowid) from mytab group by operation, interface, ocrelease, products) ;